+ Reply to Thread
Results 1 to 7 of 7

Dynamic Sheet-Index-Match-

  1. #1
    Registered User
    Join Date
    07-07-2015
    Location
    New York
    MS-Off Ver
    2016
    Posts
    26

    Dynamic Sheet-Index-Match-

    Hope all is well. I am new to this forum, pleasure to meeting you all.

    I am currently trying to write a index match formula that would have a dynamic reference linked to the sheet name I pick from a drop down.

    I have attached the example file.

    For example in =Master!D3 there is a dropdown to pick the sheet I want the data to be pulled from at which point cell =Master!E8 and =Master!E9 autofill with the the data points from the respective sheet.

    I've been banging my head all night and can't get it to work. I can get Index/Match to work off of one sheet of data but can't get it to work when referencing another dynamic sheet.

    Any help would be appreciated.
    Attached Files Attached Files

  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 2301
    Posts
    38,508

    Re: Dynamic Sheet-Index-Match-

    Hi. See the attached file. i had to change the value in master d8 so that it would match something on the two source sheets
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    07-07-2015
    Location
    New York
    MS-Off Ver
    2016
    Posts
    26

    Re: Dynamic Sheet-Index-Match-

    Quote Originally Posted by Glenn Kennedy View Post
    Hi. See the attached file. i had to change the value in master d8 so that it would match something on the two source sheets
    Glenn, thank you very much for this! Is there anyway to have it reference both the row and column header to give you the value of that intersect, instead o having to specify the column to choose the data point from in the formula? So that the values would change depending on the the date and column heading?

    I'm trying to build a master sheet that would pull values from a template which may have different date ranges for example.

  4. #4
    Registered User
    Join Date
    07-07-2015
    Location
    New York
    MS-Off Ver
    2016
    Posts
    26

    Re: Dynamic Sheet-Index-Match-

    Attached is the updated sheet, I used this formula:

    =INDEX(INDIRECT("'"&$D$3&"'!A1:H18"),(MATCH($D8,INDIRECT("'"&$D$3&"'!D:D"),0)),(MATCH(E$7,INDIRECT("'"&$D$3&"'!7:7"),0)))

    Is this the most efficient way in your opinion to do this?

    Thank you for your help.
    Attached Files Attached Files

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

    Re: Dynamic Sheet-Index-Match-

    Yes, but only up to a point. I'd wrap it up in an error trap =iferror(yourformula,"") to get rid of the #N/A errors. One thing puzzles me though. Why did you change the range of the INDEX range to cover columns well to the left of the data (A1:H18)? Probably it was to allow you to use whole row references.. INDIRECT is a "volatile" function, which recalculates everything any time something changes. This can slow things down a lot. It would be better to decide on your likely maximum range and stick to that.

    I adjused it to look for items down to row 100 (Master sheet) and for dates across as far as Z7 (Example sheets). See attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-07-2015
    Location
    New York
    MS-Off Ver
    2016
    Posts
    26

    Re: Dynamic Sheet-Index-Match-

    I was attempting to encompass the entire sheet so I wouldn't have to adjust the ranges as the spreadsheet grew on a monthly basis, historically or on a go forward basis. But I'm going to take your advice and and keep it to a minimal number of rows and columns to prevent crashing, especially since there will be many tabs. The rows are set and aren't going to grow, if anything it'll be the columns.

    Thank you for all your help, much appreciated!

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

    Re: Dynamic Sheet-Index-Match-

    Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

+ 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] Make an index and match function dynamic
    By concatch in forum Excel General
    Replies: 3
    Last Post: 08-18-2014, 02:35 PM
  2. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  3. [SOLVED] Dynamic Lookup / Index Match
    By mrgreek in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2013, 05:44 PM
  4. Vba index and match for dynamic range
    By _google in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-20-2013, 10:25 AM
  5. [SOLVED] index/match, Sumproduct and a dynamic list
    By pauldaddyadams in forum Excel General
    Replies: 23
    Last Post: 05-23-2012, 02:09 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