In the formula get cell value as a row number

[motilulla]

Hello,</SPAN></SPAN>

I am using the formula below, which is FD$6:FD$5006 this has end row number 5006 I need it pick the last row number from the cell value of B3 like "FD$6:FD$"&B3 is it can be done</SPAN></SPAN>

Book1
	FE
6	2241
Sheet1

Cell Formulas
Range	Formula
FE6	{=IF(MAX((FD6=FD$6:FD$5006)*(ROW(FD$6:FD$5006)))=0,,MAX((FD6=FD$6:FD$5006)*(ROW(FD$6:FD$5006))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

I tried to modified using INDIRECT function in the formula to get cell value of B3 but it does not seems to work please need help </SPAN></SPAN>

Book1
	FB
6	2242
Sheet1

Cell Formulas
Range	Formula
FB6	{=$B$3-IF(MAX((FD6=INDIRECT("FD$6:FD$"&B3)*(ROW(INDIRECT("FD$6:FD$"&B3)))))=0,,MAX((FD6=INDIRECT("FD$6:FD$"&B3)*(ROW(INDIRECT("FD$6:FD$"&B3))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Bump...If formula is not workable may be VBA solution please </SPAN></SPAN>

 

[Fluff]

Maybe
=IF(MAX((FD6=INDIRECT("FD$6:FD$"&B3))*(ROW(INDIRECT("FD$6:FD$"&B3))))=0,,MAX((FD6=INDIRECT("FD$6:FD$"&B3))*(ROW(INDIRECT("FD$6:FD$"&B3)))))

 

[motilulla]

Maybe
=IF(MAX((FD6=INDIRECT("FD$6:FD$"&B3))*(ROW(INDIRECT("FD$6:FD$"&B3))))=0,,MAX((FD6=INDIRECT("FD$6:FD$"&B3))*(ROW(INDIRECT("FD$6:FD$"&B3)))))

Brilliant Fluff, Your formula has solved my problem. It worked fine. Thank you very much for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[Fluff]

You're welcome & thanks for the feedback