Formula to consolidate 130 sheets into a summary sheet

[Willie G]

I know this topic has been mentioned before; but I'm having a hard time searching for the right words to get the right answer (don't think a VBA is needed).

I have approx 130 sheets that are all in the same format (Budget for each property). On the consolidated sheets (BS and PL by month) I want to pull the GL Account Number (or just the cell) for each sheet for each period. I know there is an easier way (maybe an array?) to pull the data instead of =1070!D15+2501!D15+imhfc!D15...etc (Sheet names are property names) for eachtab/account/period.

I am unfamiliar with how to build an Array or use the Sumproduct formula tobuild this formula. Your help is appreciated...thank you in advanced.

William

 

[Claus Busch]

Hi William,

Am Wed, 27 Aug 2014 10:44:24 -0700 (PDT) schrieb Willie G:

I know this topic has been mentioned before; but I'm having a hard time searching for the right words to get the right answer (don't think a VBA is needed).

I have approx 130 sheets that are all in the same format (Budget for each property). On the consolidated sheets (BS and PL by month) I want to pull the GL Account Number (or just the cell) for each sheet for each period. I know there is an easier way (maybe an array?) to pull the data instead of =1070!D15+2501!D15+imhfc!D15...etc (Sheet names are property names) for each tab/account/period.

try:
=SUM(Sheet1:Sheet130!D15)
modify the sheet name of the first and the last sheet to suit.


Regards
Claus B.

 

[Willie G]

WOW CLAUS!!!!! I can't believe it was that easy to do...didn't think that was even an option...the powers of Excel.

MANY MANY THANKS!!!!!!
William

 

[Claus Busch]

Hi William,

Am Wed, 27 Aug 2014 11:42:50 -0700 (PDT) schrieb Willie G:

I can't believe it was that easy to do...didn't think that was even an option...the powers of Excel.

you only have to pay attention that sheets that should not be included
in the sum formula are in front or behind the sum sheets. There should
be no other sheet between the sum sheets.


Regards
Claus B.

 

[Willie G]

Will this formula also work if a sheet is hidden?

The range of sheets will be at the end of the workbook, so there won't be any other sheets in between the ones that are part of the Sum( formula; but good to know.

 

[Willie G]

Can I use that same methodology in a vlookup, sumif or sumifs formula?

One of the other sheets that I need to populate is based on specific GL accounts, so I can't necessary use a 'set cell' (D15) because of the order or the range of accounts on the other sheets can vary.

Thanks,
William

 

[Claus Busch]

Hi William,

Am Wed, 27 Aug 2014 12:23:25 -0700 (PDT) schrieb Willie G:

Can I use that same methodology in a vlookup, sumif or sumifs formula?

One of the other sheets that I need to populate is based on specific GL accounts, so I can't necessary use a 'set cell' (D15) because of the order or the range of accounts on the other sheets can vary.

the formula works with hidden sheets also.
Sumif or Vlookup works if sheets have an index and you can call them by
index (Sheet1, Sheet2...). If the sheets have no index you have to write
each name into the formula and that is too much with 130 sheets.
In this case you need a VBA solution.


Regards
Claus B.