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 -