Is this possible with INDIRECT? The following formula works:
=SUM(Sep09:Jan09!C9)
I would like to change it to something like this (is returning a #REF! error):
=SUM(INDIRECT(B3&":"&B4&"!"&B5))
b3 is Sep09, b4 is Jan09, b5 is C9
If I could get this to work, I can plug the rest of the formula.
"SixBowls" wrote:
The formula is correct. I do not use column O.
"Roger Govier" wrote:
Hi
Are you sure that the formula you posted is correct?
It looks as though you are taking every other column between C and U, except
you have missed out column O
If you are taking every other column from the relevant sheet, then it could
be written as
=SUMPRODUCT((Sep!C9:U9)*MOD(COLUMN(Sep!C9:U9),2)=1 ))
Then, using a Summary sheet, enter in
cell A1 !C9:U9
in A2 Sep
in A3 Oct
in A4 Nov
In B2 =A2&$A$1
Copy down through cells B3:B4
in C2 you could enter
=SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2 )),2)=1))
and copy down to C3:C4
Your result required would be =SUM(C2:C4)
--
Regards
Roger Govier
"SixBowls" wrote in message
...
I have the following formula that totals data for multiple tabs. Each tab
is
a month and each tab is set-up the same.
=SUM('Sep-09:Jan-09'!C9,'Sep-09:Jan-09'!E9,'Sep-09:Jan-09'!G9,'Sep-09:Jan-09'!I9,'Sep-09:Jan-09'!K9,'Sep-09:Jan-09'!M9,'Sep-09:Jan-09'!Q9,'Sep-09:Jan-09'!S9,'Sep-09:Jan-09'!U9)
The formula works but I have 196 rows where the formula is used. I
currently do a find/replace when I add a new month or want to look at a
quarter. I would like the formula to reference two cells (b3 for what is
now
Sep-09 and b4 for what is now Jan-09) and be able to type the sheet name
in
those cells to get the period.
Also open to suggestions on a better formula.
__________ Information from ESET Smart Security, version of virus
signature database 4536 (20091023) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 4537 (20091023) __________
The message was checked by ESET Smart Security.
http://www.eset.com
.