Sum through dissimilar sheets

**BRISBANEBOB** · 01-06-2012, 01:21 AM

I am trying to sum a total sheet where the products vary and the site names getted added. The attached sample gives a getter idea.

Thanks for any ideas. I have used INDIRECT but becuase of the size of the sheet (up to 50 sites) it is far too slow. SUMIF falls over becuase the new site names are not known in advance and this mean having to re-write the SUMIF formula.

**arlu1201** · 01-06-2012, 04:15 AM

I remember doing this some years back. If you have an estimate on how many sites may get added in the coming year, you can prepare dummy tabs for them and keep with all formulae intact. You can include these tabs in the sumif formula as well. Once the site name is known, you just change the tab name, the fomula will automatically get updated with the new name.

**jpr73** · 01-06-2012, 04:30 AM

Hi,
A solution can be done with pivot table
See attached file with sheet PT consolidate all site names
Make a table on all site names sheets : automatic recognition by PT, automatic add product if necessary
The empty cells are for new entry : after new entry refresh the whole PT
You can add a new sheet site names and transform the data in table
After adding a new sheet, you need to use the pivot table wizard, go to previous and add the new sheet site names range and the field name
Don't forget to refresh the whole PT
Hope this will helps
Best regards

**kvsrinivasamurthy** · 01-06-2012, 04:38 AM

I feel the other way is to run a macro, which takes care of all worksheets irrespective of number of sheets in the file.

**e4excel** · 01-06-2012, 07:20 AM

Dear BrisbaneBob,

THis is a solution I reearched for your query...
http://www.mcgimpsey.com/excel/threedsumif.html

I have made a small list of all the Tab Names in the cOLUMN O from 06 till O9 which can also be automated but that you can do a ta later stage..

=SUMPRODUCT(SUMIF(INDIRECT("'"&$O$6:$O$9&"'!$A$6:$A$17"),A6,INDIRECT("'"&$O$6:$O$9&"'!$B$6:$B$17")))

This is just a single formula which gives the result for all the sheets....

Now you can work on getting the SHeet Names automatically in the Lists of Column O and also use an extended INDIRECT FUNCTION TO update the Range "'!$B$6:$B$17" so that you could drag the formula sideways..

SUBSTITUTE(ADDRESS(1,COLUMN(),1),"1","",1)&"6:"&SUBSTITUTE(ADDRESS(1,COLUMN(),1),"1","",1)&"17"

For getting the Range $B$6:$B$17

Hope this helps..

Warm REgards
e4excel

**e4excel** · 01-06-2012, 07:23 AM

For some strange reason the Column Alphabet is not changing, let me come back on tht

**e4excel** · 01-06-2012, 07:58 AM

I think thats the problem with your sheet as when I closed your file and tried afresh on my own file it worked...

You can alternatively use an MATCH FUNCTION instead of the COLUMN () to get more accurate results incase the columns are changed, however for all pratical purposes this should work..

Please check you settings as to why the COLUMN () function gives the same result for all the COLUMNS even after dragging sideways..

SOmeone please advise on the oddity...

ANS : Automatic from MANUAL

Regards
e4excel

**e4excel** · 01-06-2012, 03:58 PM

I think Brisbane this should work but I am just thinking a little ahead of complete Automation but which unfortunately I am unable to do...

In my formula the Range $O$6:$O$9 contains the Sheet Names excluding the Active SHeet Name "Total"...Now incase this list expands on increase in more sheets then WHat kind of formula should be used to include the same as the range expands with 2 more SHeets then the Range $O$6:$O$9 will change to $O$6:$O$11 so what formula should be used to include this change..

I have tried using the INDIRECT function but in vain..
Please do not hink that I am trying to hijack this thread I am just trying to help BBane to get an Unchanged formula which works even the NO of SHeets increases.

So, please gurus advise..

Warm REgards
e4excel

**BRISBANEBOB** · 01-07-2012, 02:54 AM

Thanks to all for suggestions. They all work great so I will try them in the field. All responses much appreciated.

**e4excel** · 01-07-2012, 07:39 AM

Brisbane,

I think you need to look at this thread as it will be helpful..

http://www.excelforum.com/excel-prog...html?p=2676542

I have used your sample file as an example so that it completes the Automation..

Warm Regards
e4excel