|
Cell address in a range
Suppose I have data in a range of B5:D50. In col B (B5:B50) I have certain values. Like this: B1 tt B2 bb B3 gg B4 ss B5 pp B6 kk B7 bb B8 dd and so on... to B50 I want that when I enter any value (which is present in B5:B50) in cell B52, formula in cell B53 should check it in range B5:B50 and return cell name in which that value resides. suppose I enter bb in B52 now cell B53 shoud return B2 (its cell name in the range). Note that if values appears two times it should return cell name of first value appeared in the range. (as in the case of bb) thanking in anticipation of quick reply. -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=538333 |
Cell address in a range
this formula do not work for any other range. MATCH returns row number counting from first cell of range, not original row number. Ardus Petus Wrote: =ADDRESS(MATCH(B52,B1:B50,0),2,4) [/color] -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=538333 |
Cell address in a range
=ADDRESS(ROW(B1)+MATCH(B52,B1:B50,0)+1,COLUMN(B1), 4)
HTH -- AP "starguy" a écrit dans le message de news: ... this formula do not work for any other range. MATCH returns row number counting from first cell of range, not original row number. Ardus Petus Wrote: =ADDRESS(MATCH(B52,B1:B50,0),2,4) -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=538333 [/color] |
Cell address in a range
Ooops: typo!
=ADDRESS(ROW(B1)+MATCH(B52,B1:B50,0)-1,COLUMN(B1),4) -- AP "starguy" a écrit dans le message de news: ... this formula do not work for any other range. MATCH returns row number counting from first cell of range, not original row number. Ardus Petus Wrote: =ADDRESS(MATCH(B52,B1:B50,0),2,4) -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=538333 [/color] |
Cell address in a range
thank you, this works well. one thing more. I want to return the cell name next to to cell that contains specified value. e.g. if cell B20 contains the value, I want to return next cell name that is B21. how can I do this. -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=538333 |
Cell address in a range
=ADDRESS(ROW(B1)+MATCH(B52,B1:B50,0),COLUMN(B1),4)
"starguy" a écrit dans le message de news: ... thank you, this works well. one thing more. I want to return the cell name next to to cell that contains specified value. e.g. if cell B20 contains the value, I want to return next cell name that is B21. how can I do this. -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=538333 |
Cell address in a range
okay I got it by removing -1 -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=538333 |
| All times are GMT +1. The time now is 06:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com