Excel $ help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am looking to use the formula =Sheet1!A6 in a column, with the formula
changing each cell you go down, to =Sheet2!A6, =sheet3!A6 etc.... so it is
the sheet that changes, not the cell the data is getting taken from....how do
i do this? I think it is with the $ symbol, but i cant get it to work! Any
help will be appreciated!

Thanks in advance

Fiona
 
I'm going to do this with a helper column so that you see what's happening.

A2 = 1
A3 = A2+1
A4 = A3 + 1

etc.
B2: =INDIRECT("Sheet"&A2&"!A6")
Copy down
 
Or, if you'd rather not use a helper column, you can use the ROW() function.

=INDIRECT("Sheet"&ROW()&"!A6")

If placed in B2, it would return the value of Sheet2!A6. Since B2 is in row
2. You could also use ROW()+1 or ROW()-1 to adjust if the row your formula
is in doesn't match up with the sheet number you want to reference.

HTH,
Elkar
 
Back
Top