In this post I´ll show you how to do the same using only excel formulas.

Create unique distinct column headers

Array formula in B20:

=INDEX($B$4:$B$13, MATCH(0, COUNTIF($A$20:A20, $B$4:$B$13), 0)) + CTRL + SHIFT + ENTER.

Copy (CTRL + C) cell B20 and paste (Ctrl + V) into cells C20 and D20. See picture below.

Categorize cell values into each column

Array formula in B21:

=INDEX($C$4:$C$13, SMALL(IF($B$4:$B$13=B$20, ROW($B$4:$B$13)-MIN(ROW($B$4:$B$13))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER

Copy (CTRL + C) cell B21 and paste (Ctrl + V) into cells B21:C23 and D21:D24. See picture below.

Download excel example file
Categorize data into multiple columns (formulas).xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

SMALL(array,k) returns the k-th smallest row number in this data set.

Related posts:

1. Categorize values into multiple columns using vba in excel
2. Unique distinct values from multiple columns using array formula
3. Lookup two index columns returning multiple matches in excel
4. Sort cell values into categories, part 2
5. Find missing numbers in a range from multiple columns
6. Sum adjacent values using multiple lookup text values in a column in excel
7. Return multiple values if in range in excel
8. Extract largest values from two columns using array formula in excel
9. Lookup two index columns using min max values and a date range as criteria
10. How to filter values between 0.5 and 1.5 from two columns in excel 2007

In this post I am going to categorize values from a list into unique columns.I am sure there are more efficent ways to accomplish this task using better written code. In  a year from now  I hope I´ll be laughing at this post.

Before:

After:

The code

Sub Categorizedatatocolumns()

Dim rng As Range

Dim dest As Range

Dim vrb As Boolean

Dim i As Integer

Set rng = Sheets("Sheet1").Range("A4")

vrb = False

Do While rng <> ""

Set dest = Sheets("Sheet1").Range("A20")

Do While dest <> ""

If rng.Value = dest.Value Then

vrb = True

End If

Set dest = dest.Offset(0, 1)

Loop

If vrb = False Then

dest.Value = rng.Value

dest.Font.bold = True

End If

vrb = False

Set rng = rng.Offset(1, 0)

Loop

Set rng = Sheets("Sheet1").Range("A4")

Do While rng <> ""

Set dest = Sheets("Sheet1").Range("A20")

Do While dest <> ""

If rng.Value = dest.Value Then

i = 0

Do While dest <> ""

Set dest = dest.Offset(1, 0)

i = i + 1

Loop

Set rng = rng.Offset(0, 1)

dest.Value = rng.Value

Set rng = rng.Offset(0, -1)

Set dest = dest.Offset(-i, 0)

End If

Set dest = dest.Offset(0, 1)

Loop

Set rng = rng.Offset(1, 0)

Loop

End Sub

Download excel tutorial file

Remember to backup your excel workbook, you can´t undo macros.
Categorize-data-into-multiple-columns.xls
(Excel 97-2003  Workbook *.xls)
You need to enable macros.

Related posts:

1. Split data across multiple sheets in excel (vba)
2. Categorize values into multiple columns (excel formulas)
3. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
4. Count unique distinct numbers across multiple sheets (3D range) in excel
5. Search three columns on three sheets, part 2
6. Unique distinct values from multiple columns using array formula
7. Scan stock markets in excel
8. Find missing numbers in a range from multiple columns
9. Lookup two index columns returning multiple matches in excel
10. Search three columns on three sheets

Count unique distinct numbers in a 3D range

Array formula in E8:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0)) + CTRL + SHIFT + ENTER

Count unique numbers in a 3D range

Array formula in E10:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)=1)) + CTRL + SHIFT + ENTER

Count duplicate numbers in a 3D range

Array formula in E12:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)>1)) + CTRL + SHIFT + ENTER

Download excel tutorial file

Count unique and duplicate numerical data entries from multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

Functions in this article:

SUM(number1,[number2],)
Adds all the numbers in a range of cells

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

Related posts:

1. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
2. Count unique distinct text values in a range in excel
3. Count unique distinct values using date criteria in a range in excel
4. Count unique distinct records in a date range and a numeric range in excel
5. Count unique and unique distinct values in a multicolumn range in excel
6. Combine data from multiple sheets in excel
7. Count unique records by date in excel
8. Count unique distinct values in a column in excel
9. Count unique distinct values in two columns with date criteria in excel
10. Count matching cell values in two columns in excel

Values across multiple sheets are sometimes refered to as a 3D range.

Calculate min and max values in 3D range

Formula in C6:

=MIN(Sheet1:Sheet3!$B$2:$D$4) + Enter

Formula in E6:

=Max(Sheet1:Sheet3!$B$2:$D$4) + Enter

Extract unique distinct values from a 3D range

Unique distinct values are all values in a range or column but duplicates are merged into one distinct value. See example picture below.

I am using the calculated min and max values (bolded) in the array formula in B9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))<>0, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Extract unique values from a 3D range

Unique values are values existing only once in a list or range. See example picture below.

I am using the calculated min and max values (bolded) also in the array formula in E9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))=1, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Extract duplicate values from a 3D range

Once again I am using the calculated min and max values (bolded) in the array formula in G9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))>1, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Putting it all together

In cell B18, E18 and G18 I have "merged" the min max formulas with the unique/duplicate formulas.

Array formula in B18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))<>0, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in E18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))=1, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in G18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))>1, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Keep in mind, indirect is a volatile function.

Download excel tutorial file

Return unique and duplicate numerical data entries from multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) Returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

INDIRECT(ref_text;[a1])
Returns the reference specified by a text string

Related posts:

1. Count unique distinct numbers across multiple sheets (3D range) in excel
2. Combine data from multiple sheets in excel
3. Split data across multiple sheets in excel (vba)
4. Return multiple values if in range in excel
5. Return multiple values if above frequency criterion in excel
6. Search three columns on three sheets, part 2
7. Lookup values in a range using two or more criteria and return multiple matches in excel
8. Vlookup with 2 or more lookup criteria and return multiple matches in excel
9. Create a unique distinct text list from a range containing both numerical and text values in excel
10. Extract duplicate text values from a range containing both numerical and text values in excel

Before:

After:

The Code

Option Explicit

Sub Splitdatatosheets()
'
' Splitdatatosheets Macro
'

'
Dim rng As Range

Dim rng1 As Range

Dim vrb As Boolean

Dim sht As Worksheet

Set rng = Sheets("Sheet1").Range("A4")

Set rng1 = Sheets("Sheet1").Range("A4:D4")

vrb = False

Do While rng <> ""

For Each sht In Worksheets

If sht.Name = rng.Value Then

sht.Select

Range("A2").Select

Do While Selection <> ""

ActiveCell.Offset(1, 0).Activate

Loop

rng1.Copy ActiveCell

ActiveCell.Offset(1, 0).Activate

Set rng1 = rng1.Offset(1, 0)

Set rng = rng.Offset(1, 0)

vrb = True

End If

Next sht

If vrb = False Then

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = rng.Value

Sheets("Sheet1").Range("A3:B3").Copy ActiveSheet.Range("A1")

Range("A2").Select

Do While Selection <> ""

ActiveCell.Offset(1, 0).Activate

Loop

rng1.Copy ActiveCell

Set rng1 = rng1.Offset(1, 0)

Set rng = rng.Offset(1, 0)

End If

vrb = False

Loop

End Sub

Download excel tutorial file

Remember to enable macros and backup your excel file because you can´t undo macros.

Split data across multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

Related posts:

1. Categorize values into multiple columns using vba in excel
2. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
3. Combine data from multiple sheets in excel
4. Count unique distinct numbers across multiple sheets (3D range) in excel
5. Create a unique distinct list of a long list without sacrificing performance using vba in excel
6. Search three columns on three sheets, part 2
7. Scan stock markets in excel
8. Search for multiple text strings in multiple cells and use in data validation in excel
9. Split first and last names in excel and then sort them alphabetically
10. Create a dynamic stock chart using a web query and a drop down list in excel

Problem description (simplified of course):

I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). This is in a Work book, on a TAB. Each TAB is a separate month (first is Jan, 2nd is Feb, etc.). I have 12 tabs (12 worksheets) in workbook. Each TAB, a single month, has a list of ID numbers. Some IDs may repeat on different worksheets, that is, some may be in multiple months and some may be in just two or three months or just one month. An ID number will shown only once in a month for a single task (duty). Abbreviated example is below.

Is it possible to combine the data, by function, or formula, or VBasic) to a 13th worksheet automatically and:

1. Show a list of all ID numbers in order (without repeating).
2. Show Jan data in col B, Feb data in col C, etc., and some columns will be blank because the ID had no assignment that month, and will not be on the worksheet for that month.

Is there a formula, or function, or does it have to be done in VBasic? (Is it even possible?)

I have the workbook with 12 tabs in it, and now have to manually put the ID columns side by side and copy and slide down one side on the other to get them to match, and repeat the process 12 times to get the yearly data on one worksheet.

Ex:
For Jan:
ID Duty Asgn.
01 C
05 F
09 D
15 X
23 P

For Feb:
ID Duty Asgn.
02 M
05 Q
08 A
12 R
20 W

Combing Jan and Feb would be:
ID Duty Asgn.
01 C
02 M
05 F Q
08 A
09 D
12 R
15 X
20 W
23 P

This would be repeated for each month to build all 12 col months.

Very Respectfully,
Dave Bonar

Answer:

First I thought your question required vba but here is the answer using only Excel formulas.

Maximum ID number from multiple sheets

Formula in cell B1:

=MAX(Jan:Mar!A2:A1000) + Enter

Create a sorted unique distinct list of numbers originating from multiple sheets

We are going to use the number calculated in B1 (28) in array formula in cell A4 (bolded):

=SMALL(IF(FREQUENCY(Jan:Mar!$A$2:$A$1000, ROW($1:$28))<>0, ROW($1:$28), ""), ROW(A1)) + CTRL + SHIFT + ENTER copied down as far as needed.

Combine data from multiple sheets

Formula in B4:

=IF(ISERROR(MATCH(A4, Jan!$A$2:$A$10, 0)), "", INDEX(Jan!$B$2:$B$10, MATCH(A4, Jan!$A$2:$A$10, 0))) + CTRL + SHIFT + ENTER copied down as far as needed.

Formula in C4:

=IF(ISERROR(MATCH(A4, Feb!$A$2:$A$10, 0)), "", INDEX(Feb!$B$2:$B$10, MATCH(A4, Feb!$A$2:$A$10, 0))) + CTRL + SHIFT + ENTER copied down as far as needed.

Formula in D4:

=IF(ISERROR(MATCH(A4, Mar!$A$2:$A$10, 0)), "", INDEX(Mar!$B$2:$B$10, MATCH(A4, Mar!$A$2:$A$10, 0))) + CTRL + SHIFT + ENTER copied down as far as needed.

Download excel tutorial file

Get data from each sheet.xls
(Excel 97-2003  Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference) Returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

Related posts:

1. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
2. Split data across multiple sheets in excel (vba)
3. Count unique distinct numbers across multiple sheets (3D range) in excel
4. Search for multiple text strings in multiple cells and use in data validation in excel
5. Lookup between two lists of data to highlight missing data using conditional formatting in excel
6. Create a list with most recent data available in excel
7. Lookup with multiple criteria and display multiple search results using excel formula, part 3
8. Unique distinct values from multiple columns using array formula
9. Looking up data in a cross reference table in excel
10. Return multiple values if in range in excel

I will explain how I created this calendar in an upcoming post. You can download the excel calendar file here: Excel calendar.xlsm You need to enable macros to use this calendar.

Instructions:

Select a week

1. Select a week using spinner buttons or type a date in date cells

How to add a record

1. Double click a cell
2. Type text in title window and text window
3. Click Save button on userform

How to delete a record

1. Double click a cell
2. Click Delete button on userform

Overview Calendar

The overview calendar makes spinner button navigation easier. The selected week is colored gray and the date today is yellow.

Download excel tutorial file

Excel calendar.xlsm
(Excel 2007  Workbook *.xlsm)

Related posts:

1. How to create excel macro to color every second row
2. How to create a unique list using conditional formatting in excel 2007
3. Insert a new row at the top every time a value has been entered
4. Using Excel Solver to schedule employees
5. Identify numbers in sum using solver in excel
6. Advanced custom date filter in Excel 2007
7. Dynamic stock chart in excel – Add date ranges
8. Highlight the second or more duplicates in two lists using conditional formatting in excel
9. Create a dynamic stock chart using a web query and a drop down list in excel
10. Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel

• Introduction
• Create a lookup table
• Create a drop down list
• Change web query parameter
• Add dynamic ranges to stock chart

Introduction

I have previously posted how to create  a dynamic stock chart in excel. It is really easy to use:

• Select Company
• Click "Update chart"

In this post I have added a new feature. You can now select a date range in a drop down list and the stock chart uses the new date range.

• Select Company
• Select date range
• Click "Update chart"

I am working with the attached file in this post:
Create a dynamic stock chart using a web query and a drop down list in excel
or download the complete tutorial file for this post here:
dynamic_stock_chart_change_date_range.xls

Create a lookup table

Select sheet "Data".

Formula in B1:=TODAY() + ENTER
Formula in B2:=YEAR(B1)+ENTER
Formula in B3:=MONTH(B1) + ENTER
Formula in B4:=DAY(B1)
Formula in B5:=INDEX(Overview!C3:C33, MATCH(Overview!E3, Overview!B3:B33, 0)) + ENTER

Formula in E2:=INDEX(G3:K3, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER
Formula in E3:=INDEX(G4:K4, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER
Formula in E4:=INDEX(G5:K5, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER

Formula in G2: =DATE(YEAR($B$1), MONTH($B$1)-3, DAY($B$1)) + ENTER
Formula in G3:=YEAR(G2) + ENTER
Formula in G4:=MONTH(G2)-1 + ENTER
Formula in G5:=DAY(G2) + ENTER

Formula in H2:=DATE(YEAR($B$1), MONTH($B$1)-6, DAY($B$1)) + ENTER
Formula in H3:=YEAR(H2) + ENTER
Formula in H4:=MONTH(H2)-1 + ENTER
Formula in H5:=DAY(H2) + ENTER

Formula in I2:=DATE(YEAR($B$1), MONTH($B$1)-12, DAY($B$1)) + ENTER
Formula in I3:=YEAR(I2) + ENTER
Formula in I4:=MONTH(I2)-1 + ENTER
Formula in I5:=DAY(I2) + ENTER

Formula in J2:=DATE(YEAR($B$1), MONTH($B$1)-24, DAY($B$1)) + ENTER
Formula in J3:=YEAR(J2) + ENTER
Formula in J4:=MONTH(J2)-1 + ENTER
Formula in J5:=DAY(J2) + ENTER

Formula in K2:=DATE(YEAR($B$1), MONTH($B$1)-60, DAY($B$1)) + ENTER
Formula in K3:=YEAR(J2) + ENTER
Formula in K4:=MONTH(J2)-1 + ENTER
Formula in K5:=DAY(J2) + ENTER

Create a drop down list

1. Select sheet "Data"
2. Type "3 months" in cell M1
3. Type "6 months" in cell M2
4. Type "1 year" in cell M3
5. Type "2 years" in cell M4
6. Type "5 years" in cell M5
7. Select M1:M5 and create a named range. I named it "daterange".
8. Select sheet "Overview"
9. Select cell E4
10. Select tab "Data"
11. Click "Data validation"
12. Click "Data validation..."
13. Select List
14. Type "=daterange" in source.
15. Click ok!

Change web query parameter

I have changed web query parameter "ticker" to cell reference:B5. Remaining parameters.

Add dynamic ranges to stock chart

Dynamic ranges

1. Click tab "Formulas"
2. Click "Name Manager"
3. Click "New.."
4. Name it "High"
5. Copy and paste this formula into "Refers to:": =OFFSET(Data!$C$11, 0, 0, COUNTA(Data!$C$10:$C$10000)-1, 1)
6. Click OK!
7. Click "New.."
8. Name it "Low"
9. Copy and paste this formula into "Refers to:": =OFFSET(Data!$D$11, 0, 0, COUNTA(Data!$D$10:$D$10000)-1, 1)
10. Click OK!
11. Click "New.."
12. Name it "Close"
13. Copy and paste this formula into "Refers to:":=OFFSET(Data!$E$11, 0, 0, COUNTA(Data!$E$10:$E$10000)-1, 1)
14. Click OK!

Update stock chart with dynamic ranges

1. Right click on stock chart
2. Click "Select Data"
3. Select "High"
4. Click "Edit"
5. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!High
6. Click OK
7. Select "Low"
8. Click "Edit"
9. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!Low
10. Select "Close"
11. Click "Edit"
12. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!Close
13. Click OK!
14. Click OK!

Download excel tutorial file

dynamic_stock_chart_change_date_range.xls
(Excel 97-2003 Workbook *.xls)

Related posts:

1. Create a dynamic stock chart using a web query and a drop down list in excel
2. Scan stock markets in excel
3. Stock alerts in excel
4. Create a dynamic border to your list using excel conditional formatting
5. Color every second row using dynamic conditional formatting in excel
6. Prevent duplicates using dynamic conditional formatting in excel
7. Advanced custom date filter in Excel 2007
8. Create a date range using excel formula
9. Sorting date ranges in excel
10. Extract dates using a drop down list in excel

You can find the question in this post: Extract dates using a drop down list in excel

Answer:

Array formula in C2:

=TEXT(INDEX($A$2:$A$135, MATCH(0, COUNTIF($C$1:C1, TEXT($A$2:$A$135, "mmm-yyyy")), 0)), "mmm-yyyy") + CTRL + SHIFT + ENTER copied down as far as needed.

Download excel tutorial file

unique distinct year and month.xls
(Excel2007  Workbook *.xlsx)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

TEXT(value, format_text)
Converts a value to text in a specific number format

Related posts:

1. Extract distinct unique sorted year and month list from a date series in excel
2. Count unique distinct months in excel
3. How to calculate missing months in a given date range in excel
4. Create a unique distinct list from a date range in excel
5. How to create a unique distinct list where other columns meet two criteria
6. Create a unique distinct list and sort by occurrances from large to small
7. Count unique distinct values in two columns with date criteria in excel
8. Create unique distinct list sorted based on text length using array formula in excel
9. Count unique distinct values using date criteria in a range in excel
10. Create a unique distinct text list from a range containing both numerical and text values in excel

• Create list of companies and corresponding tickers
• Create a dynamic range
• Create a drop down list
• Calculate dates
• Create a web query
• Create a macro to refresh web query and automate "text to columns"
• Create a stock chart
• Create a button

Here is a picture of the final result:

At the end of this post is an attached excel tutorial file.

Create list of companies and corresponding tickers

In this tutorial I have two sheets, Overview and Data. On sheet Overview I created a small list of companies and  corresponding ticker. You can search tickers at http://finance.yahoo.com/

Create a dynamic range

As I add new companies and tickers to the list I want the drop down list automatically updated. To accomplish this I need to create a dynamic named range.

1. Click tab "Formulas"
2. Click "Named ranges"
3. Click "New"
4. Name your range (formula in this tutorial).
   I named it "Dynrange".
5. Type in "Refers to:" =OFFSET(Overview!$B$3, 0, 0, COUNTA(Overview!$B:$B)-1, 1)
6. Click OK!

Create a drop down list

1. Select cell E3
2. Click "Data" tab
3. Click "Data validation"
4. Click "Data validation..."

   

5. Select Allow: List
6. Type in "Source:" =Dynrange
7. Click OK!

Calculate dates

To download the latest three months of historical data we need to calculate the the date three months back.

I did this calculation on sheet "Data". The formulas use the current date today automatically.

Cell H1 matches the corresponding ticker to the selected company in the drop down list.

See formula details on the attached file at the bottom of this post.

Create a web query

1. Click "Data" tab
2. Click "From web"
3. Paste this into address field:http://table.finance.yahoo.com/table.csv?a=["fm","fm"]&b=["fd","fd"]&c=["fy","fy"]&d=["tm","tm"]&e=["td","td"]&f=["ty","ty"]&s=["ticker", "ticker"]&y=0&g=d&ignore=.csv
4. Click "Go"
5. Click "Import"
6. Select cell "A10" on sheet "Data"
7. Click OK

   
   

8. Select cell E3 (fm) . Enable "Use this value/reference for future refreshes". Click OK.
9. Select cell E4 (fd) . Enable "Use this value/reference for future refreshes". Click OK.
10. Select cell E2 (fy) . Enable "Use this value/reference for future refreshes". Click OK.
11. Select cell B3 (tm) . Enable "Use this value/reference for future refreshes". Click OK.
12. Select cell B4 (td) . Enable "Use this value/reference for future refreshes". Click OK.
13. Select cell B2 (ty) . Enable "Use this value/reference for future refreshes". Click OK.
14. Select cell H1 (ticker) . Enable "Use this value/reference for future refreshes". Click OK.

VBA code to refresh web query and automate "text to columns".

1. Click "Developer" tab
   How to show the Developer tab or run in developer mode
2. Click "Visual Basic"
3. Create a "Module" for your workbook
   How to Copy Excel VBA Code to a Regular Module
4. Copy this vba code into module:

Sub Refreshwebq()

Set qryTableStocks = ThisWorkbook.Worksheets("Data").QueryTables(1)

With qryTableStocks
.BackgroundQuery = False
End With

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("Data").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

ActiveWorkbook.Connections("Connection").Refresh

Sheets("Data").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A10"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True

Sheets("Overview").Select

End Sub

Create a stock chart

1. Select High, Low and Close columns on sheet "Data"
2. Click "Insert" tab
3. Click "Other charts"
4. Click High-Low-Close stock chart
5. Cut and paste stock chart to sheet "Overview"
6. Right click on x-axis values
7. Click "Select data"
8. Click "Edit" in Horizontal (Category) axis labels
9. Select Dates on sheet "Data", =Data!$A$11:$A$71
10. Click OK!
11. Click OK!
12. Right click on x-axis values
13. Click "Format axis"
14. Click Axis Options
15. Click Axis Type: Text axis
16. Click Categories in reverse order
17. Click Vertical axis crosses: At maximum category
18. Click Close

Create a button and assign macro Refreshwebq()

How to Run an Excel Macro With a Worksheet Button

Download excel tutorial file

dynamic stock chart using web query.xls
(Excel 97-2003 Workbook *.xls)

Related posts:

1. Dynamic stock chart in excel – Add date ranges
2. Stock alerts in excel
3. Create a dynamic border to your list using excel conditional formatting
4. How to create excel macro to color every second row
5. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
6. Extract dates using a drop down list in excel
7. Color every second row using dynamic conditional formatting in excel
8. Create a unique distinct list of a long list without sacrificing performance using vba in excel
9. Scan stock markets in excel
10. How to create a unique list using conditional formatting in excel 2007