Many Sheets Sum Data

[msquared99]

First, thanks for reading my post.

I have 9 sheets in a spreadsheet. The year is in column A and the month is in row 1. All the sheets are laid out the same.

I have a summary sheet. You can select the month and year.

How do I get the numbers from sheets 1 thru 9 based on the selection of the month and year in the summary sheet?

So, if I select Jan 1988 how do I get the data from sheets 1 thru 9 that correspond to the intersection Jan 1988 from each sheet?

I have tried Indirect(Month) Indirect(Year) in each sheet but it does not work.

Thanks!

Mike

 

[Somendra Misra]

Hi Mike,

If you can upload a file it will be easier to see your data layout and somebody can give you a solution.

Regards,

 

[Somendra Misra]

Hi,

See the file.

Is this what you are looking at?

Regards,

 

[NARAYANK991]

Hi Misra / Mike ,

Try this :

=SUM(N(INDIRECT(SheetsList&"!"&"R"&MATCH($C$2,INDIRECT("'"&$B$8&"'!"&"A1:A17"),0)&"C"&MATCH($C$3,INDIRECT("'"&$B$8&"'!"&"A1:M1"),0),FALSE)))

entered as an array formula , using CTRL SHIFT ENTER ; SheetsList is a named range referring to the range B8:B9 which has the names of the individual sheets.

Narayan

 

[Somendra Misra]

Nice formula @NARAYANK991 sir. Learnt something new, we can pass an array in Indirect function as sheet names.
Gr8
Thanks.

Regards,

 

[msquared99]

OK here is the file.

I have not tried Narayan's formula yet but will.

I am trying to do this without having to name a bunch of ranges.

Thanks,

Mike

 

[msquared99]

OK, so I'm lost on this formula given. What are the cell references referring to?

 

[NARAYANK991]

Hi Mike ,

See your file.

Narayan

 

[msquared99]

AWESOME!!!!

Now I get it.

I could not see the forest for the trees!

Thank you for your help!