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

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

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!!!

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)),"")