+ Reply to Thread
Results 1 to 5 of 5

INDEX MATCH with dynamic reference using INDIRECT

  1. #1
    Registered User
    Join Date
    02-11-2019
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    3

    INDEX MATCH with dynamic reference using INDIRECT

    Hi! Excel Professionals.

    In need of help in regards to Index Match array with dynamic reference using INDIRECT function, have attached two files that is relative to each other.

    first file consolidate,
    -have successfully using indirect function data within workbook (balance sheet)

    Second file STATUS,
    -failed using same formula structure to get dynamic reference.

    for reference: I left some notes for further observation.

    Thank you hoping for a response.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: INDEX MATCH with dynamic reference using INDIRECT

    You can't use INDIRECT on closed workbook.

    I assume you want the formula to work when workbook is closed? If that's the case, you'll either need to use custom INDIRECT UDF (do a Google search and you should find couple).
    However, these will have limitations as well.

    Another method is to use named range. See link below.
    https://support.office.com/en-us/art...a-d7cca2a9b95f
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    02-11-2019
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    3

    Re: INDEX MATCH with dynamic reference using INDIRECT

    Hi! CK76 thank you for the quick response, i did some intensive research for the pass 2 days, force me to register here to ask some help, i also want the formula to work (open workbook) so i may be more familiarize which step i got it wrong. thank you

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: INDEX MATCH with dynamic reference using INDIRECT

    It isn't clear to me what exactly you are trying to achieve here.
    CC is only tracked in Balance sheet & January sheet. Yet, you are referencing ABC sheet...

    As well, your data set up isn't ideal for data analysis or lookup operation. I'd recommend setting up flat table to perform your operation.

  5. #5
    Registered User
    Join Date
    02-11-2019
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    3

    Re: INDEX MATCH with dynamic reference using INDIRECT

    Hi CK76!

    both files are just mock files they are not the actual files being used, the files is more inclined to a calculator rather than to a data analysis type and the sheets i entered in the mock files are just samples. thanks will take the advice on your first post, having to know that indirect cannot work on close workbook, will try another formula. (will tag the thread as solved)

+ 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. Dynamic Index Match Match with Indirect
    By apzoe in forum Excel General
    Replies: 2
    Last Post: 01-01-2019, 06:32 PM
  2. [SOLVED] INDEX MATCH - INDIRECT cell reference.
    By LeeBillington in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2018, 02:48 PM
  3. [SOLVED] Index Match with dynamic external reference (closed file)
    By doq7 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-29-2017, 10:52 AM
  4. Replies: 7
    Last Post: 01-10-2017, 11:11 PM
  5. [SOLVED] Dynamic reference in Index-Match function
    By GIS2013 in forum Excel General
    Replies: 3
    Last Post: 12-29-2016, 06:08 PM
  6. [SOLVED] Using INDEX, MATCH, and INDIRECT to reference cells in other sheets
    By jgilbe31 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-14-2015, 02:02 PM
  7. Replies: 5
    Last Post: 01-30-2015, 06:39 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