Dynamic Sum

[Steve]

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?

 

[Bill Kuunders]

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

 

[T. Valko]

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]

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!!

 

[Steve]

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!

 

[T. Valko]

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 Insert>Name>Define
Names in workbook: type the name in here: sNames
Refers to: =Input!$A$2:$A$6
OK out

Biff

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!!

 

[T. Valko]

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

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!

 

[Roger Govier]

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))))

 

[T. Valko]

Yeah, that'll work. In fact, that is less confusing than:

CHAR(COLUMNS($A:A)+64)&"1"

Biff

 

[Steve]

Thanks guys!! Much appreciated!

Yeah, that'll work. In fact, that is less confusing than:

CHAR(COLUMNS($A:A)+64)&"1"

Biff







- Show quoted text -

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thanks guys!! Much appreciated!