+ Reply to Thread
Results 1 to 3 of 3

Error using Address + Indirect with Array

  1. #1
    Forum Contributor jomaor1's Avatar
    Join Date
    10-08-2018
    Location
    Brasil
    MS-Off Ver
    Excel 2016
    Posts
    177

    Error using Address + Indirect with Array

    Hi, I've been having a headache since yesterday trying to come up with a solution to my problem.
    I created an array formula based on a complicated factor we have on our Spreadsheet. You actually don't have to understand the whole formula,
    but rather just the part where is going wrong, as shown below:

    {=INDEX(INDIRECT(LEFT(ADDRESS(ROW(A530);168+MATCH(I530;$FM$2:$RN$2;0));3)&"$2:"&LEFT(ADDRESS(ROW(A530);200+MATCH(I530;$FM$2:$RN$2;0));3)&"$2");MATCH(TRUE;ISBLANK(INDIRECT(ADDRESS(530;168+MATCH(I530;$FM$2:$RN$2;0))&":"&ADDRESS(530;200+MATCH(I530;$FM$2:$RN$2;0))));0))-1}

    The Green is doing it's thing right, but I need to change the Red.
    The way it's written above is returning no error, but I need to "pull" the formula down, and the "530" needs to become 531,532...and so on.

    If I use "ROW(A530)" for Instance, it returns an #N/A error, and the Error Evaluation shows that after all the formulas inside the Address are made,
    when it comes to the Address, it shows like this:

    "INDIRECT("$HC$530:"&ADDRESS({530};243))"

    And then:

    "INDIRECT("$HC$530:"&{"$II$530"})"
    = #N/A

    Does someone have any idea how the first half of the formula didn't bring the brackets, and the second half brought this annoying guys => "{}" ?

    Thanks in advance, John.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Error using Address + Indirect with Array

    I wonder if below would work for you - confirmed with Enter (no Array entry required, but still volatile given OFFSET [rather than INDIRECT])


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the above should return the header value from row 2 (less 1) associated with first blank found in variable range in row 530, 531 etc...

    edit: modified to 33 (from 32) columns wide
    Last edited by XLent; 02-18-2019 at 08:24 AM.

  3. #3
    Forum Contributor jomaor1's Avatar
    Join Date
    10-08-2018
    Location
    Brasil
    MS-Off Ver
    Excel 2016
    Posts
    177

    Re: Error using Address + Indirect with Array

    It worked!!, but now I've encountered another "logical issue" with my formula...nonetheless thank you! You've solved this thread problem.

+ 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. Make part of address indirect and keep rest of address relative?
    By OldManExcellor in forum Excel General
    Replies: 1
    Last Post: 01-29-2016, 06:07 AM
  2. How to indirect address the date in VB's Array statement
    By bernoulli in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2012, 11:58 PM
  3. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 12:05 PM
  4. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

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