| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FIND the first (and any) number within a cell | Excel Discussion (Misc queries) | |||
| find a number in a cell | Excel Discussion (Misc queries) | |||
| Is there a way to find out whether theres a number in a cell?? | Excel Discussion (Misc queries) | |||
| find an exact number in a different sheet and use the cell containing the number to output information | Excel Programming | |||
| How to find and sub total a specific number of cell above the target cell | Excel Programming | |||
Linear Mode
