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

1. ## 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:

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:

I get a Value error...

Thanks

2. ## Re: Replacing cell address in a formula with the result of ADDRESS function

Not checked
This required a to be addressed with indirect function .

Punnam

4. ## Re: Replacing cell address in a formula with the result of ADDRESS function

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.

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

5. ## Re: Replacing cell address in a formula with the result of ADDRESS function

Hi.

You don't need to use volatile INDIRECT/ADDRESS constructions here. Simply INDEX will suffice:

Regards

6. ## Re: Replacing cell address in a formula with the result of ADDRESS function

Solved...Thank You all!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1