Dynamic Sum

"Steve" <steve_a...@yahoo.com> wrote in message
news:1175804251.2...@d57g2000hsg.googlegroups.com...

On Apr 5, 5:11 pm, "T. Valko" <biffinp...@comcast.net> 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:
>

>
> news:1175804251.2...@d57g2000hsg.googlegroups.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

On Apr 5, 5:11 pm, "T. Valko" <biffinp...@comcast.net> 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
>

>
> news:1175804251.2...@d57g2000hsg.googlegroups.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

"Steve" <steve_a...@yahoo.com> wrote in message

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?

On Apr 5, 5:11 pm, "T. Valko" <biffinp...@comcast.net> 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:
>

"Steve" <steve_a...@yahoo.com> wrote in message

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" <biffinp...@comcast.net> 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:
>

On Apr 5, 10:00 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Yeah, that'll work. In fact, that is less confusing than:
>
> CHAR(COLUMNS($A:A)+64)&"1"
>
> Biff
>

>
> news:uOWlVu9d...@TK2MSFTNGP06.phx.gbl...
>
>
>
> > 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
>

> >news:OJYwqG9d...@TK2MSFTNGP05.phx.gbl...
> >> >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"!
>

>
> >> That'll work up to Z1.
>
> >> After that, you're on your own!
>
> >> Biff
>

> >> "Steve" <steve_andrus...@yahoo.com> wrote in message

> >>news:1175809214.6...@o5g2000hsb.googlegroups.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" <biffinp...@comcast.net> 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:
>

>
> >>> Biff
>
> >>> "Steve" <steve_andrus...@yahoo.com> wrote in message
>
> >>>news:1175804251.2...@d57g2000hsg.googlegroups.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 -
>

"Steve" <steve_a...@yahoo.com> wrote in message

On Apr 5, 10:00 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Yeah, that'll work. In fact, that is less confusing than:
>
> CHAR(COLUMNS($A:A)+64)&"1"
>
> Biff
>
> "Roger Govier" <r...@technologyNOSPAM4u.co.uk> wrote in message
>
> news:uOWlVu9d...@TK2MSFTNGP06.phx.gbl...
>
>
>
> > 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" <biffinp...@comcast.net> wrote in message
> >news:OJYwqG9d...@TK2MSFTNGP05.phx.gbl...
> >> >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"!
>

>
> >> That'll work up to Z1.
>
> >> After that, you're on your own!
>
> >> Biff
>
> >> "Steve" <steve_andrus...@yahoo.com> wrote in message
> >>news:1175809214.6...@o5g2000hsb.googlegroups.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" <biffinp...@comcast.net> 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:
>