|
text function?
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. |
text function?
how many sheets you have between Sep-09 to Jan-09
is it Sep-09,Aug-09,Jul-09,Jun-09,May-09,Apr-09,Mar-09,Feb-09,Jan-09 On Oct 23, 8:14*pm, SixBowls wrote: 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. |
text function?
Here is a suggestion on your formula which you pretty have most of it done
already. I would set up each spreadsheet the same. So the totals are in the same cell on each page. Insert a tab calling it First before your first spreadsheet and add another spreadsheet after the last of your spreadsheets calling it Last. You can hide these after you are done..but do not enter spreadsheets in between these two tabs unless you want them to also be in your totals. Create a summary tab and in the same cell enter "=SUM(First:Last!I27)" This will total everything into the summary page. -- If this has helped you, please click Yes Thanks, Megan "SixBowls" wrote: 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. |
text function?
Thanks for the reply. That will get my contract to date totals. However, my
spreadsheet will have 60 months (Sep-06 through Aug-11) and I need to pull data for periods of time - usually calendar years or quarters. I am playing around with the INDIRECT function now but can't seem to get it to work. "Megan" wrote: Here is a suggestion on your formula which you pretty have most of it done already. I would set up each spreadsheet the same. So the totals are in the same cell on each page. Insert a tab calling it First before your first spreadsheet and add another spreadsheet after the last of your spreadsheets calling it Last. You can hide these after you are done..but do not enter spreadsheets in between these two tabs unless you want them to also be in your totals. Create a summary tab and in the same cell enter "=SUM(First:Last!I27)" This will total everything into the summary page. -- If this has helped you, please click Yes Thanks, Megan "SixBowls" wrote: 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. |
text function?
Nine - just as you listed. It will have 60 when complete (Sep-06 to Aug-11).
"muddan madhu" wrote: how many sheets you have between Sep-09 to Jan-09 is it Sep-09,Aug-09,Jul-09,Jun-09,May-09,Apr-09,Mar-09,Feb-09,Jan-09 On Oct 23, 8:14 pm, SixBowls wrote: 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. . |
text function?
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 |
text function?
Hi
I noticed an error in my posting. The values to enter in A2 onward of your Summary sheet, would of course be Jan-09, Feb-09, Mar-09 etc. Having just seen your last posting, where you say you will have 60 sheets, clearly the series will move on through till Dec-14 Just complete the series down column A, and achieve your values in Column B You can then easily pick out your Quarterly and annual totals from this column of 60 results. -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... 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 __________ Information from ESET Smart Security, version of virus signature database 4538 (20091024) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4538 (20091024) __________ The message was checked by ESET Smart Security. http://www.eset.com |
text function?
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 . |
text function?
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 . |
text function?
Hi
Well then, you could just use =SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2 )),2)=1))-INDIRECT(A2&"!O9") Taking the single value of O9 away from the total would still be far more efficient than adding up all of the individual cells. -- Regards Roger Govier "SixBowls" wrote in message ... 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 . __________ Information from ESET Smart Security, version of virus signature database 4545 (20091026) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4545 (20091026) __________ The message was checked by ESET Smart Security. http://www.eset.com |
text function?
column o has text and the formula gives me an error. I got it to work by
changing the formula to: =SUMPRODUCT((INDIRECT(C3))*(MOD(COLUMN(INDIRECT(C3 )),2)=1))+SUMPRODUCT((INDIRECT(D3))*(MOD(COLUMN(IN DIRECT(D3)),2)=1)) c3 is sheet name + c9:m9 and d3 is sheet name + q9:u9 Thanks for the help! Shawn "Roger Govier" wrote: Hi Well then, you could just use =SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2 )),2)=1))-INDIRECT(A2&"!O9") Taking the single value of O9 away from the total would still be far more efficient than adding up all of the individual cells. -- Regards Roger Govier "SixBowls" wrote in message ... 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 . __________ Information from ESET Smart Security, version of virus signature database 4545 (20091026) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4545 (20091026) __________ The message was checked by ESET Smart Security. http://www.eset.com . |
text function?
You're more than welcome Shawn
I hadn't thought about the possibility that column O contained Text!!! Glad you figured out the workaround to the problem. -- Regards Roger Govier "SixBowls" wrote in message ... column o has text and the formula gives me an error. I got it to work by changing the formula to: =SUMPRODUCT((INDIRECT(C3))*(MOD(COLUMN(INDIRECT(C3 )),2)=1))+SUMPRODUCT((INDIRECT(D3))*(MOD(COLUMN(IN DIRECT(D3)),2)=1)) c3 is sheet name + c9:m9 and d3 is sheet name + q9:u9 Thanks for the help! Shawn "Roger Govier" wrote: Hi Well then, you could just use =SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2 )),2)=1))-INDIRECT(A2&"!O9") Taking the single value of O9 away from the total would still be far more efficient than adding up all of the individual cells. -- Regards Roger Govier "SixBowls" wrote in message ... 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 . __________ Information from ESET Smart Security, version of virus signature database 4545 (20091026) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4545 (20091026) __________ The message was checked by ESET Smart Security. http://www.eset.com . __________ Information from ESET Smart Security, version of virus signature database 4549 (20091027) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4549 (20091027) __________ The message was checked by ESET Smart Security. http://www.eset.com |
| All times are GMT +1. The time now is 05:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com