Summing column and rows
 

Hello Excel users and Experts,

=C1+INDIRECT(CHAR(ROW()+67)&1)

With this formula in B1 and pulled down will add C1 & D1, C2 & E1, C3 & F1
etc.

How can I make INDIRECT(CHAR(ROW()+67)&1) reference sheet 2 with C1, C2, C3
on sheet 1?

So it sums C1 of sheet 1 and D1 of sheet 2
C2 of sheet 1 and E1 of sheet 2
and so on.

Thanks for any ideas.

Regards,
Howard

Summing column and rows
 

=C1+INDIRECT("Sheet2!"&CHAR(ROW()+67)&1)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"L. Howard Kittle" wrote in message
...
Hello Excel users and Experts,

=C1+INDIRECT(CHAR(ROW()+67)&1)

With this formula in B1 and pulled down will add C1 & D1, C2 & E1, C3 & F1
etc.

How can I make INDIRECT(CHAR(ROW()+67)&1) reference sheet 2 with C1, C2,
C3
on sheet 1?

So it sums C1 of sheet 1 and D1 of sheet 2
C2 of sheet 1 and E1 of sheet 2
and so on.

Thanks for any ideas.

Regards,
Howard

Summing column and rows
 

Try this:

=C1+INDIRECT("Sheet2!"&CHAR(ROW()+67)&1)

Or, a non-volatile approach:

=C1+INDEX(Sheet2!D$1:IV$1,ROWS($1:1))

Biff

"L. Howard Kittle" wrote in message
...
Hello Excel users and Experts,

=C1+INDIRECT(CHAR(ROW()+67)&1)

With this formula in B1 and pulled down will add C1 & D1, C2 & E1, C3 & F1
etc.

How can I make INDIRECT(CHAR(ROW()+67)&1) reference sheet 2 with C1, C2,
C3 on sheet 1?

So it sums C1 of sheet 1 and D1 of sheet 2
C2 of sheet 1 and E1 of sheet 2
and so on.

Thanks for any ideas.

Regards,
Howard

Summing column and rows
 

Hey Bob & Biff,

Many thanks for the solutions guy's!!

O'boy, I was a hog sniff'n pretty close to the truffle, but could not get
that "sheet2!" in the right place or the "(" & ")" in the right places.

And Biff, the non volatile formula is another tribute to your many
contributions. Exactly what I was looking for. I had no idea it could be
done that way.

Thanks to both of you.

PS: This is for my wife's project and she thinks I'm an Excel genius. Go
figure, but I know where to get answers!!! She doen't need to know... hee
hee.

Thanks again.

Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hello Excel users and Experts,

=C1+INDIRECT(CHAR(ROW()+67)&1)

With this formula in B1 and pulled down will add C1 & D1, C2 & E1, C3 & F1
etc.

How can I make INDIRECT(CHAR(ROW()+67)&1) reference sheet 2 with C1, C2,
C3 on sheet 1?

So it sums C1 of sheet 1 and D1 of sheet 2
C2 of sheet 1 and E1 of sheet 2
and so on.

Thanks for any ideas.

Regards,
Howard

Summing column and rows
 

You're welcome. Thanks for the feedback!

Biff

"L. Howard Kittle" wrote in message
. ..
Hey Bob & Biff,

Many thanks for the solutions guy's!!

O'boy, I was a hog sniff'n pretty close to the truffle, but could not get
that "sheet2!" in the right place or the "(" & ")" in the right places.

And Biff, the non volatile formula is another tribute to your many
contributions. Exactly what I was looking for. I had no idea it could be
done that way.

Thanks to both of you.

PS: This is for my wife's project and she thinks I'm an Excel genius. Go
figure, but I know where to get answers!!! She doen't need to know... hee
hee.

Thanks again.

Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hello Excel users and Experts,

=C1+INDIRECT(CHAR(ROW()+67)&1)

With this formula in B1 and pulled down will add C1 & D1, C2 & E1, C3 &
F1 etc.

How can I make INDIRECT(CHAR(ROW()+67)&1) reference sheet 2 with C1, C2,
C3 on sheet 1?

So it sums C1 of sheet 1 and D1 of sheet 2
C2 of sheet 1 and E1 of sheet 2
and so on.

Thanks for any ideas.

Regards,
Howard