Autofill a Sheet Reference

[JOJO193]

I am referencing a sheet and would like to autofill the sheet reference.

For example I have the cell ='LA5'!$B$5 . I want to auto fill to LA6, LA7,
LA8 and so on.

How do you do this?

Thanks JOJO193

 

[Mayte]

take the $ from 5, mianing ... ='LA5'!$B5 and copy down. you'll the change to
='LA5'!$B6, ='LA5'!$B7, ='LA5'!$B8 and so on ..

 

[Mayte]

sorry for the typos ..

take the $ from 5, mianing ... ='LA5'!$B5 and copy down. you'll the change to
='LA5'!$B6, ='LA5'!$B7, ='LA5'!$B8 and so on ..

 

[JOJO193]

Mayte, I want the cell to stay constant but I want the sheet to increase.
Like this ='LA5'!$B$6, ='LA6'!$B$6, and so on. Can this be done?

 

[Gary''s Student]

Try:

=INDIRECT("LA" & ROWS($1:1)+4 & "!$B$5")

and copy down. The sheet reference will increase, but the cell reference
will be the same.

 

[David Biddulph]

=INDIRECT("'LA"&ROW(A5)&"'!$B$5")

 

[David McRitchie]

Don't know if it would help or not but it might make sense to put the
worksheet name into Column A (which could be a company name), then
in column B place a formula to extract the value in Cell B2 of a company sheet,
this formula includes a hyperlink to take you to cell A1 of the company sheet,
to return to original summary sheet use Alt+ArrowLt (which you can
set up on a five button mouse).

B2: =IF(TRIM(A2)="","",IF(ISERROR(INDIRECT("'" & A2 & "'!$A$1")),"missing sheet", HYPERLINK("#" & "'" & A2 & "'!$A$1",INDIRECT("'" &
A2 & "'!$B$2")) ))

 

[David McRitchie]

Seems to me you got the answers you asked for, did you try them.
I offered you a solution that did not match your arctual request,
but might be useful if you are working on a summary sheet.

By the way your sheetnames look like cell addresses.
If you put the LA5 into a cell you could use the fill handle
and it would increment the numeric portion. Not what you
asked because you are not showing the sheetname.