| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Sum
Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input
sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
One way,
=IF(B2=1,Sheet1!A1,0)+IF(B3=1,Sheet2!A1,0)+IF(B4=1 ,Sheet3!A1,0)+IF(B5=1,Sheet4!A1,0)+IF(B6=1,Sheet5! A1,0) Greetings from New Zealand "Steve" wrote in message oups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
If you only have a "few" sheets then I would probably use Bill's suggestion.
If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNames&"'!A1"))) Biff "Steve" wrote in message oups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
Hi Biff. Thanks so much for the response. I hate to sound dumb, but
can you elaborate a little? How do I create a defined name for all the sheets? And do I create it for ALL the sheet names? Thanks Biff!! On Apr 5, 5:11 pm, "T. Valko" wrote: If you only have a "few" sheets then I would probably use Bill's suggestion. If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNa*mes&"'!A1"))) Biff "Steve" wrote in message oups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B?- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
Biff,
Disregard previous post. I got it! I have one follow up question if I may - is it possible to make the cell being summed (in this case A1) dynamic as well? Meaning, if I copy the formula down one row, can I get it to sum cell B1? Thanks! On Apr 5, 5:11 pm, "T. Valko" wrote: If you only have a "few" sheets then I would probably use Bill's suggestion. If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNa*mes&"'!A1"))) Biff "Steve" wrote in message oups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B?- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
OK....
Assume on your Input sheet you have: A2 = Sheet1 A3 = Sheet2 A4 = Sheet3 A5 = Sheet4 A6 = Sheet5 Select the range A2:A6 Click in the Name box (that box directly above the "A" in column A) and type in a name for that range. I used the name sNames in my example. sNames for sheet names. That's the easy way to assign a name to range. Another way: Goto the menu InsertNameDefine Names in workbook: type the name in he sNames Refers to: =Input!$A$2:$A$6 OK out Biff "Steve" wrote in message oups.com... Hi Biff. Thanks so much for the response. I hate to sound dumb, but can you elaborate a little? How do I create a defined name for all the sheets? And do I create it for ALL the sheet names? Thanks Biff!! On Apr 5, 5:11 pm, "T. Valko" wrote: If you only have a "few" sheets then I would probably use Bill's suggestion. If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNa*mes&"'!A1"))) Biff "Steve" wrote in message oups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B?- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
if I copy the formula down one row, can I
get it to sum cell B1? Do you mean if you copy it *across* a row? Ok, now you're getting a little "hairy"! =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNames&"'!"&CHAR( COLUMNS($A:A)+64)&"1"))) That'll work up to Z1. After that, you're on your own! Biff "Steve" wrote in message ups.com... Biff, Disregard previous post. I got it! I have one follow up question if I may - is it possible to make the cell being summed (in this case A1) dynamic as well? Meaning, if I copy the formula down one row, can I get it to sum cell B1? Thanks! On Apr 5, 5:11 pm, "T. Valko" wrote: If you only have a "few" sheets then I would probably use Bill's suggestion. If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNa*mes&"'!A1"))) Biff "Steve" wrote in message oups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B?- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
Hi Biff
Maybe the following would allow you to go past Z1 =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)- MIN(ROW(B2:B6)),),1, INDIRECT("'"&sNames&"'!"&ADDRESS(1,COLUMN(A1),4))) ) -- Regards Roger Govier "T. Valko" wrote in message ... if I copy the formula down one row, can I get it to sum cell B1? Do you mean if you copy it *across* a row? Ok, now you're getting a little "hairy"! =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNames&"'!"&CHAR( COLUMNS($A:A)+64)&"1"))) That'll work up to Z1. After that, you're on your own! Biff "Steve" wrote in message ups.com... Biff, Disregard previous post. I got it! I have one follow up question if I may - is it possible to make the cell being summed (in this case A1) dynamic as well? Meaning, if I copy the formula down one row, can I get it to sum cell B1? Thanks! On Apr 5, 5:11 pm, "T. Valko" wrote: If you only have a "few" sheets then I would probably use Bill's suggestion. If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNa*mes&"'!A1"))) Biff "Steve" wrote in message oups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B?- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
Yeah, that'll work. In fact, that is less confusing than:
CHAR(COLUMNS($A:A)+64)&"1" Biff "Roger Govier" wrote in message ... Hi Biff Maybe the following would allow you to go past Z1 =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)- MIN(ROW(B2:B6)),),1, INDIRECT("'"&sNames&"'!"&ADDRESS(1,COLUMN(A1),4))) ) -- Regards Roger Govier "T. Valko" wrote in message ... if I copy the formula down one row, can I get it to sum cell B1? Do you mean if you copy it *across* a row? Ok, now you're getting a little "hairy"! =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNames&"'!"&CHAR( COLUMNS($A:A)+64)&"1"))) That'll work up to Z1. After that, you're on your own! Biff "Steve" wrote in message ups.com... Biff, Disregard previous post. I got it! I have one follow up question if I may - is it possible to make the cell being summed (in this case A1) dynamic as well? Meaning, if I copy the formula down one row, can I get it to sum cell B1? Thanks! On Apr 5, 5:11 pm, "T. Valko" wrote: If you only have a "few" sheets then I would probably use Bill's suggestion. If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNa*mes&"'!A1"))) Biff "Steve" wrote in message oups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B?- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
Thanks guys!! Much appreciated!
On Apr 5, 10:00 pm, "T. Valko" wrote: Yeah, that'll work. In fact, that is less confusing than: CHAR(COLUMNS($A:A)+64)&"1" Biff "Roger Govier" wrote in message ... Hi Biff Maybe the following would allow you to go past Z1 =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)- MIN(ROW(B2:B6)),),1, INDIRECT("'"&sNames&"'!"&ADDRESS(1,COLUMN(A1),4))) ) -- Regards Roger Govier "T. Valko" wrote in message ... if I copy the formula down one row, can I get it to sum cell B1? Do you mean if you copy it *across* a row? Ok, now you're getting a little "hairy"! =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sN*ames&"'!"&CHAR (COLUMNS($A:A)+64)&"1"))) That'll work up to Z1. After that, you're on your own! Biff "Steve" wrote in message roups.com... Biff, Disregard previous post. I got it! I have one follow up question if I may - is it possible to make the cell being summed (in this case A1) dynamic as well? Meaning, if I copy the formula down one row, can I get it to sum cell B1? Thanks! On Apr 5, 5:11 pm, "T. Valko" wrote: If you only have a "few" sheets then I would probably use Bill's suggestion. If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNa**mes&"'!A1")) ) Biff "Steve" wrote in message egroups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Sum
You're welcome. Thanks for the feedback!
Biff "Steve" wrote in message ups.com... Thanks guys!! Much appreciated! On Apr 5, 10:00 pm, "T. Valko" wrote: Yeah, that'll work. In fact, that is less confusing than: CHAR(COLUMNS($A:A)+64)&"1" Biff "Roger Govier" wrote in message ... Hi Biff Maybe the following would allow you to go past Z1 =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)- MIN(ROW(B2:B6)),),1, INDIRECT("'"&sNames&"'!"&ADDRESS(1,COLUMN(A1),4))) ) -- Regards Roger Govier "T. Valko" wrote in message ... if I copy the formula down one row, can I get it to sum cell B1? Do you mean if you copy it *across* a row? Ok, now you're getting a little "hairy"! =SUMPRODUCT(SUMIF(OFFSET($B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sN*ames&"'!"&CHAR (COLUMNS($A:A)+64)&"1"))) That'll work up to Z1. After that, you're on your own! Biff "Steve" wrote in message roups.com... Biff, Disregard previous post. I got it! I have one follow up question if I may - is it possible to make the cell being summed (in this case A1) dynamic as well? Meaning, if I copy the formula down one row, can I get it to sum cell B1? Thanks! On Apr 5, 5:11 pm, "T. Valko" wrote: If you only have a "few" sheets then I would probably use Bill's suggestion. If you have "a lot" of sheets then this will work: Give your list of sheet names a defined name. Something like sNames. Then: =SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&sNa**mes&"'!A1")) ) Biff "Steve" wrote in message egroups.com... Hello. I have 5 sheets (named Sheet1 though Sheet5). On an input sheet, I wrote sheet1 though sheet5 in cells A2:A6, with a 1 or 0 in column B. Is there a way sum cell A1 in each of the sheets that have a 1 next to it in column B?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
| Make dynamic charts more dynamic | Charts and Charting in Excel | |||
| Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
![Steve[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
