Summary Tab for Workbook

[Red Dianthus]

I am having some difficulty in determining how best to create a
workbook.

First, it will contain a tab/worksheet for each month/year. Each
worksheet contains a production schedule with target and actual dates
for each source file scheduled for that month. Some source files are
scheduled for weekly or daily receipt so could be recurring during a
single month.

Secondly, I need to create a summary worksheet where I can populate it
based on a selection from a dropdown box of each available worksheet/
month. I've got the dropdown box being populated just now need to
figure out how to reference that to populate the summary worksheet.

Any ideas?

Now for some of my recurring files, I may have a need to only
represent the most recent date on the summary worksheet so how would I
manage to identify the most recent date and pull that into the
summary?

I know this isn't described very well so let me know if I can clarify
anything.

 

[smartin]

I am having some difficulty in determining how best to create a
workbook.

First, it will contain a tab/worksheet for each month/year. Each
worksheet contains a production schedule with target and actual dates
for each source file scheduled for that month. Some source files are
scheduled for weekly or daily receipt so could be recurring during a
single month.

Secondly, I need to create a summary worksheet where I can populate it
based on a selection from a dropdown box of each available worksheet/
month. I've got the dropdown box being populated just now need to
figure out how to reference that to populate the summary worksheet.

Any ideas?

Now for some of my recurring files, I may have a need to only
represent the most recent date on the summary worksheet so how would I
manage to identify the most recent date and pull that into the
summary?

I know this isn't described very well so let me know if I can clarify
anything.

Let's assume that, based on the value selected in the dropdown, you can
obtain the name of the selected worksheet and place it in cell A2 of the
summary worksheet. How you do this will depend on how you implemented
the dropdown.

Let's assume further that the data sheets are organized like this simple
table:

A B
Date Data
7/1/09 1
7/2/09 2

Back on the summary tab, set up a couple helper formulas:
Key Range @ B2 : ="'"&A2&"'!A:A"
Data Range @ C2 : ="'"&A2&"'!B:B"

These two formulas build up strings that will serve us in the remaining
effort. It is important to realize that "A:A" and "B:B" are hard-coded
to indicate your key/lookup values and data values, respectively.

Total of selected worksheet Data: =SUM(INDIRECT(C2))
Most recent Date in selected worksheet @ E2: =MAX(INDIRECT(B2))
Data value on most recent Date: =SUMIF(INDIRECT(B2),E2,INDIRECT(C2))

Hope this gives you a start.