Sum across sheets sumif indirect match index

[dgdarnall]

Hello,
Have searched the forum for a bit and can't seem to find an easy answer to what I think is an easy question.

I have a worksheet with P&L tabs for about 120 store locations with a number of fiscal periods across, and I want to do a summary sheet P&L at end, for any and all accounts by fiscal period. The P&L's are nearly all identical, but some don't have as many accounts as others, so the number of rows and accounts is not consistent.

So for instance, column b will always have the accounts, like 'net sales' in it somewhere. Some stores might have 'overtime labor', some not. Then columns C through AC are the fiscal periods. Is there an easy way to have excel sum across all the sheets onto a summary P&L sheet showing all accounts with one account per row with fiscal period columns going across. (along the lines of a sumif or vlookup across tabs)

I thought I was pretty good with excel , but have never used the indirect, match, or index functions and this has me stumped. I don't want to have to make a stupid list of the tab names and all that crud if I don't have to. Besides, from what I have found with that, I couldn't easily sum everything to one line.

Is there an easy way out ? (i.e. a relatively simple single cell formula)

using xcel 2003

thanks in advance,
Dave

 

[GlennUK]

Use a pivottable, Multiple Consolidation Ranges, and add the data area for each sheet ( I've never done it for 120 sheets, I must admit ) with column B the leftmost part of the area and the fiscal periods the top of the area, and hey presto, it's done.

 

[dgdarnall]

Thanks! That did the trick. I've never used that feature, and it was right under my nose the whole time.

Is there anything a Pivot Table can't do ?

 

[GlennUK]

That's great!

Ha, ha, PivotTables are great ... BUT there are some very annoying things about them that will drive you crazy if you use them a lot. Mind you, they seem to get better and better with every new version of Excel.