Help with formula ='1'!$H$19+'1'!$H$57

  • Thread starter Thread starter Mike Birney
  • Start date Start date
M

Mike Birney

How would I go about changing or writing the formula: ='1'!$H$19+'1'!$H$57
so that the next rows are
='2'!$H$19+'2'!$H$57
='3'!$H$19+'3'!$H$57

without copying the formula and just manually changing it on each row?
Mike
 
Try this.

Assume you're entering these formulas starting in cell A1:

=INDIRECT("'"&ROWS(A$1:A1)&"'!H19")+INDIRECT("'"&ROWS(A$1:A1)&"'!H57")

Copy down as needed.
 
The '1' in the formula is not a row. It is the name of a worksheet. The
formula is adding cells h19 and h57 on a worksheet named 1.
 
If in fact you have worksheets named 1, 2, 3 etc, I no of no way to change
the sheet name in the formula without VBA programming.
 
Use the ROW() function which returns a number to change the worksheet name if
the worksheet names are 1, 2, 3 as OP describes.

I don't believe you tested the formulas Bob and Biff posted before you said it
couldn't be done.

Enter =ROW() in a cell then drag/copy down to see what is returned.

The INDIRECT uses that number for the sheet name.


Gord Dibben MS Excel MVP
 
I can't get it to work yet, not sure what I am doing wrong, but am
playing with it some more.
The 1 is a tab number.
Mike
 
Your sheets are named 1, 2, 3 etc.

On a summary sheet named "Summary" enter this from Biff's post in A1 and copy
down.

=INDIRECT("'"&ROWS(A$1:A1)&"'!H19")+INDIRECT("'"&ROWS(A$1:A1)&"'!H57")


Gord
 
Shorter:
=INDIRECT("'"&ROW(A1)&"'!H15")+INDIRECT("'"&ROW(A1)&"'!H22")
and fill down.
Bob Umlas
 
Back
Top