Sum accross sheets using indirect

[schielrn]

Does anyone know why you cannot seem to sum accross sheets using indirect? I cannot seem to get this to work and I have tried a million different ways. I have a sheet called cumulative, but I do not want it to be all sheets, which is what I have it as currently. For this example I will just make it simple. I have a cumulative tab and 365 sheets after it for each day of 2007. What I would like is for someone to be able to type in 2 dates and get values for thall sheets including and between those 2 dates. But indirect keeps giving me a #ref error and maybe I am doing it worng. It works fine for 1 sheet using sum. Here is my example below. I have a 1 in cell A1 in sheets 01012007 through 01052007. I should expect a result of 5. 01062007 through 12312007 are blank, but that should have no impact on this.

Book1
	A	B	C	D
1	5
2	1	01012007	12312007
3	1	01012007	12312007
4	#REF!	01012007	12312007
5	#REF!	01012007	12312007
6	#REF!	1/1/2007	12/31/2007
7	#REF!	1/1/2007	12/31/2007
Cumulative

It seems to work fine except for when you want to sum accross worksheets and I haven't been able to figure out how to construct it, if it is even possible. Basically I just want people to be able to input 2 dates and be able to see a cumulative total for that time period in this cumulative sheet. I would like to stay away from VBA and UDFs because this is going to be passed out throught management and they don't always enable macros. Any help would be appreciated. Thanks.

 

[Dave Patton]

Hello schielrn

With:
- sheet names like 20070101, 20070102, etc
- Start and End are named ranges for the date criteria (Start and End Sheets) or with
just cell references

=SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(Start&":"&End)),"yyyymmdd")&"'!A1")))

or

=SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(B6&":"&C6)),"yyyymmdd")&"'!A1")))

Please adjust references and revise the Text formatting as necessary to match your sheet names.

hth Dave

 

[schielrn]

Thank you very much for your help with this. It worked perfectly.

 

[itr674]

Hello schielrn

With:
- sheet names like 20070101, 20070102, etc
- Start and End are named ranges for the date criteria (Start and End Sheets) or with
just cell references

=SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(Start&":"&End)),"yyyymmdd")&"'!A1")))

or

=SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(B6&":"&C6)),"yyyymmdd")&"'!A1")))

Please adjust references and revise the Text formatting as necessary to match your sheet names.

hth Dave

Is it possible to add a criteria to these SUMing accross sheet formulas?

 

[schielrn]

What do you mean by adding a criteria?

 

[itr674]

1. There is a four row table at the top o 365 sheets.
2. In any of the four rows might be one of ten vehicle identifiers, A1, A2, A3 etc, there will never be more than four in the table, but it can be any of the ten.
3. Each sheet is named in formatt ddmmmyy.
4. On a summary sheet I want to total by month data on those ten vehicles.

My HTML maker is functioning correctly or would post a table.

So like regularly using SUMPRODUCT I would look down a range for one item and sum another range for each time that item was found...

 

[itr674]

Been trying something like this:

F5 and F6 house first day and last day of month (1Jan08, 31Jan08)
A22 houses vehicle type range on each sheet
A21 is the vehicle criteria
E21 house the rang to sum on each sheet

It doesn't give me the correct results but it close...

=SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(F$5&":"&F$6)),"ddmmmyy")&"'!"&$A22))=$A21)+
SUMPRODUCT(N(INDIRECT("'"&TEXT(ROW(INDIRECT(F$5&":"&F$6)),"ddmmmyy")&"'!"&$E21)))