Excel Summary worksheet from multiple detail worksheets

[MissMew]

I have a Excel workbock which consists of a summary (corporate) worksheet and nine detail (plant) worksheets.

The plant worksheets contain information by month to date and year to date information for an entire year and are all set up identically. Something like this:

A B C D E

1 Val1 YTD1 Val2 YTD2 .....
2 January
3 February
. .
. .
14 Year to Date


The corporate spreadsheet summarizes the information for each plant for the CURRENT month and YTD. Something like this:

A B C D E

1 Val1 YTD1 Val2 YTD2 .....
2 Plant 1
3 Plant 2
. .
. .
10 Totals


Getting the YTD values is easy - I just point to the correct worksheet and YTD cell for each plant e.g.
=Plant1!E14. Getting the current month information has proven more difficult. I was hoping to use a Vlookup e.g. =VLOOKUP(Cur_Month, LookTable, 2, FALSE). Cur_Month is month to report and is input onto the summary sheet. LookTable is the table containing the month and the corresponding row in column 2. I can get the row number easily enough but this doesn't work because I cannot insert the lookup into the formula in place of the row number.

Is there a way to add a number to the worksheet!cell formula so that it moves to the next cell down?

Is there any other way to do this without using VBA?

Thanks for your help!

 

[sawedoff]

On the sheet where you want the Current Month data, in a cell out of view (example G1), put =month(Current_month)*2

Then in the spot where you want the current month data, put your VLOOKUP(Plant 1,Data range, G1, False) Assuming January is in col.2, and the other months follow, every other column, this should work.

Sawedoff

 

[MissMew]

Thank you very much! With your response I was able to look at the problem differently and I've got it working.

 

[sawedoff]

I knew my idea was probably not the exact solution, but if it got you on the right track, then I'm glad to help out.

Sawedoff