Use Row Number to find Cell value

[Armando]

Hello I have a question about how to get a cell value using a row number. I
currently have a formula that finds the row number of record with the largest
score. I then use the Indirect formula to take that row number to retrieve
more information about the record. Here is the formula

=INDIRECT("'Sheet1'!B"&$G$3)

(G3 is the cell that contains the row number for the record with the largest
value)

is there anyway to change the formula so that if the sheet name changes the
formula updates with the new sheet name? Or is there a better formula to use?
Thanks for the help

 

[Joel]

try Using address

=address(3,7,1,"Sheet1")

 

[Gary''s Student]

Put the sheet name in a cell, say Z100:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1)))

and then use Z100 in the indirect function.

 

[Tim Zych]

Check out the Offset function, something like:

=OFFSET(Sheet1!B1,$G$3-ROW(Sheet1!B1),0)

where B1 is the first cell in the list.