Lookup from an index that contains dynamic pages

[MrDeadMan]

I'm going to do my best to explain this issue. I have been searching for days for an appropriate answer. So, here goes.

I have a workbook that is used for productivity tracking. There is a master worksheet (Master), and separate worksheets for each day of the year (mm-dd-yy). The productivity numbers are manually entered into the Master sheet. After the end of the business day, the entire tab is copied and pasted to a daily sheet named accordingly. At this point, the Master sheet is then cleared of its data.

My issue is that I have a separate workbook for the head of the department that needs to dynamically link a few cells from the daily tabs. This VP workbook operates the same way. A master tab that is used as a current day's tab, and copied and pasted into a daily tab at the end of the business day.

Since future tabs are created at the end of the business day, I had to create an index worksheet to dynamically list new worksheets as they are created (VBA index).

My question is:
Is there a way to lookup the cells that I need referenced and have the results placed into the corresponding date row's cells without having to manually enter the formula each day? I need to know if it is possible to have the results from cell B2 in the daily sheet show in cell B3 of the Index worksheet, Daily C2 should go to Index C3, and so on. Keep in mind that the Index tab will be updated dynamically whenever a new sheet is added (Daily). So basically, I want the Index tab information to populate when the Master sheet is pasted to the Daily sheet. I hope this all makes sense.


Index worksheet:

	A	B	C	D	E
1	Index
2	Master
3	08/12/15
4	08/11/15
5	08/10/15

<tbody>
</tbody>

Daily worksheet:

	A	B	C	D	E
1		Metric1	Metric2	Metric3	Metric4
2	Manager Name 1
3	Manager Name 2
4
5

<tbody>
</tbody>

 

[MrDeadMan]

Ok.. I have made some progress with my issue. I am now using the formula:

=IF($A$3="","",VLOOKUP("ManagerName1",INDIRECT("'"&$A3&"'!$A$2:$E$3"),COLUMN(B2),0))

This appears to be working like a charm except for one major issue. The daily tabs are named using the date. No matter what format I use for the date field, I get a #REF!.

Any ideas?