+ Reply to Thread
Results 1 to 4 of 4

Dynamic sheet reference in formula INDEX MATCH usinf INDIRECT

  1. #1
    Registered User
    Join Date
    03-03-2021
    Location
    France
    MS-Off Ver
    2016
    Posts
    2

    Dynamic sheet reference in formula INDEX MATCH usinf INDIRECT

    Hello

    I am trying to make my INDEX MATCH formula dynamic for the sheet reference however I receive an error when I make the first MATCH argument INDIRECT("'"&B$2&"'!"&"$B$9:$AO$256"), dynamic using INDIRECT:

    When I use the following formula, I receive the N/A error:

    =INDEX(INDIRECT("'"&B$2&"'!"&"$B$9:$AO$256"),MATCH('Tax Analysis by Entity'!$C4,INDIRECT("'"&B$2&"'!"&"$B$9:$AO$256"),0),MATCH('Tax Analysis by Entity'!D$2,INDIRECT("'"&B$2&"'!"&"$B$8:$AO$8"),0))

    When I remove the first INDIRECT argument from the first MATCH the formula returns the correct result however the first MATCH sheet reference argument is not dynamic:

    =INDEX(INDIRECT("'"&B$2&"'!"&"$B$9:$AO$256"),MATCH('Tax Analysis by Entity'!$C4,'2018 Income Statement'!$B$9:$B$256,0),MATCH('Tax Analysis by Entity'!D$2,INDIRECT("'"&B$2&"'!"&"$B$8:$AO$8"),0))

    Can somebody assist me to resolve this problem so that all sheet references are dynamic?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: Dynamic sheet reference in formula INDEX MATCH usinf INDIRECT

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    03-03-2021
    Location
    France
    MS-Off Ver
    2016
    Posts
    2

    Re: Dynamic sheet reference in formula INDEX MATCH usinf INDIRECT

    Please find attached a sample file. I have eliminated a lot of data so that it accurately reflects what I am trying to achieve.

    Thank you in advance for your help.
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: Dynamic sheet reference in formula INDEX MATCH usinf INDIRECT

    1. The row matching term "Income from the activity" in C4 does not exist anywhere on the target sheet.

    2. You were not using INDIRECT to direct Excel to that sheet from C4.

    3. the ranges weren't quite right.

    I have manually added a value and the term "Income from the activity" to the 2018 sheet and this works:


    =INDEX(INDIRECT("'"&B$2&"'!C9:AO256"),MATCH($C4,INDIRECT("'"&B$2&"'!B9:B256"),0),MATCH('Tax Analysis by Entity'!D$2,INDIRECT("'"&B$2&"'!C8:AO8"),0))
    Attached Files Attached Files

+ 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] Help with Dynamic Sum (Sum(Indirect(Index(Match
    By mugelbbub in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-09-2019, 10:44 AM
  2. [SOLVED] INDEX MATCH with dynamic reference using INDIRECT
    By Avaritia in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2019, 11:09 PM
  3. Dynamic Index Match Match with Indirect
    By apzoe in forum Excel General
    Replies: 2
    Last Post: 01-01-2019, 06:32 PM
  4. [SOLVED] INDEX MATCH - INDIRECT cell reference.
    By LeeBillington in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2018, 02:48 PM
  5. Using INDIRECT formula to reference a dynamic range on another sheet
    By Travisty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-01-2018, 12:22 PM
  6. Replies: 5
    Last Post: 11-27-2017, 09:08 AM
  7. Replies: 7
    Last Post: 01-10-2017, 11:11 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