Use Row Number to find Cell value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Armando - 28 May 2008 19:39 GMT

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

Reply to this Message

Joel - 28 May 2008 19:52 GMT

try Using address

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

> 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
[quoted text clipped - 9 lines]
> formula updates with the new sheet name? Or is there a better formula to use?
> Thanks for the help

Reply to this Message

Gary''s Student - 28 May 2008 19:53 GMT

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.

Signature

Gary''s Student - gsnu200789

Reply to this Message

Tim Zych - 28 May 2008 20:32 GMT

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.

Signature

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

> Hello I have a question about how to get a cell value using a row number.
> I
[quoted text clipped - 14 lines]
> use?
> Thanks for the help

Reply to this Message