Display a SUM using different worksheets and dates

[rmedoc]

OK. First off, thanks for all the great help thus far. Haven't needed to post because searching has provided my answers. Until now.

I am trying to develop a daily scorecard of our plant's reportable metrics.

Each section of the plant has their requirements to post data to their specific worksheet. I need to get that information into the main scorecard area. There is the problem.

The formats on the worksheets are all exact in terms of how they are separated by periods, weeks, and days.

I need the scorecard to show the data for the day prior only on one colum, and then the week rollup on the other.

Is it possible to use this workbook in it's current setup to accomplish those requirements or do I need to scratch this whole thing and figure out a different approach?

The requirements are this:

Individual Page for each section for data
Historical and "running" data shown on those pages
Daily scorecard showing Yesterday's results and week to date

Attached is the spreadsheet. If you could figure out a formula to display the TICR date (B4 first sheet) with the TICR dates inputted starting in SAFETY worksheet (start at cell C5) it would be greatly appreciated! From there I think I can do the ol' copy and paste to get the results I need.

Thanks in advance.

Solve this problem and I will do my best to Pay it Forward.

Craig