Microsoft Office 2007 professional (aca...

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 tablePlease advice

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 Down...you 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:

=INDEX(INDIRECT($R$1),ROW()-6,COLUMN()-16)

INDIRECT($R$1) will pull the value form the Drop Down and use the Named Range as the

referencefor the INDEX function.INDEX() will pull data from the Named Range based on the

row_numandcol_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_numsandcol_numsfor 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!

Hi, 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 headersexactlyas they appear on row 1 on Sheet1

Assuming month 1 is January, then 'December' will be in cell G12In 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 & 59This 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 headingsIn 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.

Regards

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History