Macro to creat drop down list

Microsoft Office 2007 professional (aca...
March 11, 2010 at 06:53:31
Specs: Excel 2007
I have the following work sheet structure
1- 12 months column headers, under each month 4 column headers
2- 99 row headers
so each month is a table ( 101 row * 4 column) and there's a space between every month table
i want make a macro that enables me to table with the month table structure and to have drop down list in the month header so each time i choose the month from the drop down list the table displays that month details from it's table

Please advice

See More: Macro to creat drop down list

March 11, 2010 at 07:44:43
If I understand your structure correctly, I think you can do it without a macro.

Here's what I did:

1 - I created the Monthly tables then selected each table and named it to match the Month. In my test, the first cell (which contains January) was A1. Therefore A1:D101 is the Named Range "January", F1:I101 is named "February", K1:N101 is named March. (I only created 3 months for my testing.)

2 - I created a list of Months in separate range and created a Data Validation Drop Down list to choose each month. My Drop Down is in R1. Remember, I only created 3 Monthly Tables, so I have room to use R1 for my Drop can put yours wherever you need to.

Here's where it gets fun. If you don't understand this part, come on back and I'll explain it in more detail.

3 - I selected the first cell where I want my individual monthly tables to appear. In my case I chose Q7. You can choose whatever cell you want, but what you do in the next step will be based on which cell you choose.

4 - In Q7, I entered this formula:


INDIRECT($R$1) will pull the value form the Drop Down and use the Named Range as the reference for the INDEX function.

INDEX() will pull data from the Named Range based on the row_num and col_num. With this formula in Q7:

ROW() - 6 = 1 and COLUMN() - 16 = 1

Therefore with January chosen from the Drop Down, the formula evaluates to:

=INDEX(January,1,1) which will pull the value from A1.

5 - Drag this formula across 4 Columns and down 101 Rows.

The ROW() - 6 and COLUMN() - 16 functions will evaluate to the correct row_nums and col_nums for the INDEX function and return the value from those locations in the Named Ranges.

Obviously, you'll need to adjust the [-6] and [-16] values based on what Row and Column you place the first INDEX function in to ensure that you have the correct offsets.

Have Fun!

Report •

March 11, 2010 at 08:06:21

If I have got this right, you want to be able to display any month's data (4 columns * 99 rows plus headers) just by selecting the month from a drop-down list of months.

This can be done with standard formulas.

From your description of the data, there are 12 months of data, each consisting of 4 columns, with an empty column between each.

Assuming that the Month 1 data starts in column A, then the month 12 data will be in columns BD, BE, BF & BG

With all your source data on Sheet1, do this:

Goto Sheet2
In Column G, starting at cell G1, enter the twelve month headers exactly as they appear on row 1 on Sheet1
Assuming month 1 is January, then 'December' will be in cell G12

In cells H1, I1, J1 and K1 enter: 1, 2, 3 & 4
In cell H2 enter this formula: =H1+5
Drag and extend this formula across to column K
Now select all four cells H2 to K2 and drag and extend the formulas down to row 12
Cells H12 to K12 will now display: 56, 57, 58 & 59

This table is effectively an index to the columns containing the 4 columns of data for each month.

In cell A1, create a drop-down list using data validation and selecting 'List'
The list is the following range of cells: G1:G12
When you click on A1 the drop down will now show the twelve month headings

In cell A2 enter this formula: =OFFSET(Sheet1!$A2,0,VLOOKUP($A$1,$G$1:$K$12,2,FALSE)-1)
Note the $ signs - these are essential for maintaining the correct cell relationships when this formula is dragged.

Drag and extend the formula right to column D, cell D2
Now select the four cells A2 to D2 and drag and extend the formulas down to row 101
Cell D101 will contain this formula: =OFFSET(Sheet1!$A101,0,VLOOKUP($A$1,$G$1:$K$12,5,FALSE)-1)

Selecting any month from the drop-down list in cell A1 will display that month's data, starting with the four headers from row 2 on Sheet1 and all 99 rows of data.


Report •
Related Solutions

Ask Question