+ Reply to Thread
Results 1 to 8 of 8

Using INDIRECT to make index/match formula reference multiple worksheets in same workbook

  1. #1
    Registered User
    Join Date
    12-30-2012
    Location
    Louisville, KY
    MS-Off Ver
    Excel 2010
    Posts
    3

    Using INDIRECT to make index/match formula reference multiple worksheets in same workbook

    I have created a large, unweildly spreadsheet that gets daily/hourly updates. It is a production schedule that has a worksheet for each production line we schedule. The products tend to jump around (i.e. they don't go from 1st worksheet to the 2nd worksheet: A-> B -> C...It could go A->C->B, etc). I currently maintain each production line manually ensure the start/end dates are accurate and then manually move the 'end date' to the next step on another work sheet.

    My Index/Match function works (Screening, B4) as long as I manually type the name of the worksheet into the formula. I find this time consuming and would like to have the formula on the Screening worksheet in column "B" and then tell it what worksheet to pull from in column A.

    In the attached file, I'm pulling production end dates (column B) from 'WP - C84' or 'WP - CA131' into the 'Screening' worksheet (Avail to Screen, Column B) by matching the Sales Order WINS number (Column C on WP - C84 and - CA131; Column D on Screening).

    This type of formula will be used to link all 27 production lines (as needed), the screening schedule, and ultimately update a backlog sheet, so I made a much smaller file to submit for review.

    I appreciate any and all help - I'm thinking it may be a "'" or () situation, but after staring at this for the last 5 days, I give up and am asking for expert guidance. Thanks for any consideration and/or input!

    Schedule.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Using INDIRECT to make index/match formula reference multiple worksheets in same workb

    you were really really close

    =INDEX(INDIRECT("'"&A6&"'!c4:c6"),MATCH(D6,INDIRECT("'"&A6&"'!$C$4:$C$6"),0))

    you had the match type (0) inside the 2nd indirect() function
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-30-2012
    Location
    Louisville, KY
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Using INDIRECT to make index/match formula reference multiple worksheets in same workb

    Thanks - that has certainly taken care of the "REF!" error, but the value returned is incorrect. I changed the formula as you suggested and it returned 7/14/2016 instead of Schedule.xlsxSchedule.xlsx

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Using INDIRECT to make index/match formula reference multiple worksheets in same workb

    try this...
    =INDEX(INDIRECT("'"&A5&"'!$a$4:$c$6"),MATCH(D5,INDIRECT("'"&A5&"'!$C$4:$C$6"),0),1)

    note: i increased the index range to A4:C6, and added 1 "1" at the end for the 3rd part (column) of the index() function
    =index(range,row,column)

  5. #5
    Registered User
    Join Date
    12-30-2012
    Location
    Louisville, KY
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Using INDIRECT to make index/match formula reference multiple worksheets in same workb

    Thanks - I changed the "1" to a "2" to get the 2nd column to get the end date as the end date drives when the next product can start production. I'll then use the end date in an if/then function to see if the 2nd process end date (of previous product) is greater than the previous step of the product in question... (so on and so forth).

    I really appreciate the excellent help and timely response - many thanks and Happy New Year!!!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Using INDIRECT to make index/match formula reference multiple worksheets in same workb

    Happy to help, and happy new year to you as well If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

  7. #7
    Registered User
    Join Date
    01-10-2017
    Location
    USA
    MS-Off Ver
    Mac 2016
    Posts
    6

    Re: Using INDIRECT to make index/match formula reference multiple worksheets in same workb

    Hi. I am new to this thread, but I have a similar issue and can't figure it out. I am indexing and matching two different defined rows in a worksheet. The Criteria is the volume amount and if it goes above a certain number, the month it occurred is when I want to hire someone. I have it working on one worksheet. The issue is how I add an indirect to look at each of the other worksheets. The formula is listed below. The sheet names are Operating Pan '17, Operating Plan '18...etc. Any help on this would be helpful.

    =IFERROR(INDEX('Operating Plan ''17'!$D$2:$O$2,MATCH(INDEX('Operating Plan ''17'!$D$4:$O$4,MATCH(TRUE,'Operating Plan ''17'!$D$4:$O$4>=B28,0)),'Operating Plan ''17'!$D$4:$O$4,0)),"")

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Using INDIRECT to make index/match formula reference multiple worksheets in same workb

    cgbarnes77 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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