+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    09-25-2014
    Location
    Miami,Fl
    MS-Off Ver
    2003
    Posts
    4

    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

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

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

    =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

  3. #3
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

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

    Try using INDIRECT(ADDRESS(8,30))
    ________________________________________________________
    If your problem is solved, update the thread as SOLVED: Go to the top of the first post-Select Thread Tools-Select Mark thread as Solved OR - Go to the first post - Click edit- Click Advance- Just below the word "Title:" you will see a dropdown with the word No prefix.- Change to Solve- Click Save.

    Show your gratitude to the person who helped you solving your problem by clicking on star button at the bottom of such post.

  4. #4
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    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.
    - 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.

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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:

    =MATCH(TRUE,INDEX(AD:AD,2+MATCH(O3,A3:A1000,-1)):AD1000>0,0)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Registered User
    Join Date
    09-25-2014
    Location
    Miami,Fl
    MS-Off Ver
    2003
    Posts
    4

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

    Solved...Thank You all!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Using function result as cell address
    By El_engineer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-19-2013, 08:19 AM
  2. Move to the result of the address function
    By Wigglebritches in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2013, 12:37 AM
  3. [SOLVED] Return Cell Address of the min() formula result
    By ciprian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2013, 08:47 AM
  4. Convert ADDRESS() function result to literal text
    By ajetrumpet in forum Excel General
    Replies: 6
    Last Post: 03-18-2011, 04:20 PM
  5. [SOLVED] Using address function result in an array
    By RLR in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2006, 10:25 PM

Bookmarks

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