Summary Table - Is there a better way?

[misiek5510]

Hi Guys,

Is there a way to do it without Marcos / VBA as people I'm working for tend to disable marcos.
Currently I have excel files that are multi tab (anything from 2-10 tabs). with one tab being a summary tab which basically puts all the sheets into a table. How its is done right now is by referencing cells directly with if statements.
=IF('Sheet2'!$A$37=0,"",'Sheet2'!$A$6) The if statement is there to either include the values from particular sheet or not, so if a37 in sheet2 is = 0 I will not include any values for it. With 10 tabs it requires 250 000 if statements and makes the file almost 2mb on its own. Would there be better way to do it without macros and so that its being done straight away in the background.

 

[Crystalyzer]

Why not convert your IF statements to a SUM formula as below

=SUM('Sheet1'!A3,'Sheet2'!A3,'Sheet3'!A3,'Sheet4'!A3)

When ALL sheets do not have a value the SUM will return 0. If you don't want the zero, you could wrap this into a single IF statement like this

=IF(SUM('Sheet1'!A3,'Sheet2'!A3,'Sheet3'!A3,'Sheet4'!A3)<>0,SUM('Sheet1'!A3,'Sheet2'!A3,'Sheet3'!A3,'Sheet4'!A3),"")

 

[misiek5510]

This solution would not work, because it will show me the total value (sum) for all sheets and I will not know what individual sheets values were. Lets say Sheet1 is tokio and sheet2 is london and the value is population, so it would just show total and I'd like to be able to tell which town is how much in the report later on. Hope it makes sense.

 

[Crystalyzer]

Can you post sample worksheets using the XL2BB tool?

 

[misiek5510]

I'll log in to my personal PC after work to upload it

 

[misiek5510]

Sadly I don't have excel on my PC since I reinstalled windows recently. I cant attach a sample, so I'm posting screen shots.
In this example we have two tabs (London and Tokio) and each three sections with values and a Settings in A column. The summary page is directly referencing the values from the tabs, but also is looking at the settings on each tab and as it says Not to include section two, the summary page has empty rows because thats where section two would be placed if the setting was set to yes. It would works exacly the same for all sheets. I'm looking for a better way to get that data into summary page instead of using if statement for each cell.

 

[Crystalyzer]

Are the blanks only there because that is what the formula result is? Meaning, if they could be eliminated that would be ok?

 

[misiek5510]

Hi yes its because the formula is there and yes they could be eliminated