Replacing cell address in a formula with the result of ADDRESS function

Hi There,

I have the following formulas:

Formula #1: {=MATCH(TRUE,$AD$8:AD1000>0,0)} (Array Formula) and the result in my worksheet is = 2 which is correct
Formula #2: =ADDRESS(2+MATCH(O3,$A$3:$A$1000,-1),30) and the result of this formula is = $AD$8 which is correct

if I try replacing the statement $AD$8 in Formula #1, with the complete Formula #2 as follows:

{=MATCH(TRUE,ADDRESS(2+MATCH(O3,$A$3:$A$1000,-1),30):AD1000>0,0)}

I get a #Value! error

Even if I replace the statement $AD$8 in Formula #1 with a simple formula such as ADDRESS(8,30) (result = $AD$8) as follows:

{=MATCH(TRUE,ADDRESS(8,30):AD10000>0,0)}

I get a Value error...

Thanks

=MATCH(TRUE,INDIRECT(ADDRESS(8,30)&":AD10000")>0,0)
==MATCH(TRUE,INDIRECT(ADDRESS(2+MATCH(O3,$A$3:$A$1000,-1),30)&":AD1000")>0,0)
Not checked
This required a to be addressed with indirect function .

Punnam

Welcome to the forum CMG2,

It is not easy to test this formula without data, but I would suggest you try to wrap the INDIRECT function around the formula you already have : INDIRECT(ADDRESS(2+MATCH(O3,$A$3:$A$1000,-1),30)&":AD1000") - something like this!

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Solved...Thank You all!!!