Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary Tab for Workbook
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary Tab for Workbook
Red Dianthus wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary worksheet within a workbook | Excel Discussion (Misc queries) | |||
Workbook Summary Sheet2 | Excel Programming | |||
Workbook Summary Sheet | Excel Programming | |||
How to link a NEW workbook back to a summary workbook? | Excel Discussion (Misc queries) | |||
Mutiple Workbook Summary | Excel Worksheet Functions |