Use Row Number to find Cell value
 

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

Use Row Number to find Cell value
 

try Using address

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

"Armando" wrote:

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

Use Row Number to find Cell value
 

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.
--
Gary''s Student - gsnu200789


"Armando" wrote:

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

Use Row Number to find Cell value
 

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.


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"Armando" wrote in message
...
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