Linking information and extracting it from added pages

[gatornut30]

Hi,

I’m an intermediate EXCEL user and have developed an ATC training tracker for my facility. I’d like to have a summery sheet which extracts data from all training record sheet. I have developed master sheets for the 2 facilities we train in and for the 3 kinds of trainee’s we would have (Tower 3-level, non-prior rated, prior rated / Radar – 3-level, non-prior rated, prior rated). I want to keep these 6 master sheets in a workbook separate from my “status” workbook. The “status” workbook will keep track of training status but I want to add sheets from the master workbook to the “status” workbook as more people start training. When I add sheets from the master workbook to the “status” workbook I want the “main” sheet in the “status” workbook to automatically start capturing data from the new sheet that was added. I will have 6 different sheets and 6 different areas to capture the data in as appropriate. I can go through and manually add the cells that I want to average each time I add a sheet matching the criteria, but I would think there is a way to do it automatically.?.?

This is one of the cells I need to capture. On the “main” page in the “status tracker_posted” file, I want cell B6 to be the average of cell G41 across all “TWR 3-Level Master” pages (currently named “TWR TEST FILE”). There will be other pages named “TWR Non-Prior Master” and “TWR Prior Master” that utilize the same cell (G41) but that information will be placed in cell B21 or B36 respectively for the person’s prior qualification. When I add pages out of the “master” workbook into the “status” workbook I also want them to automatically begin to generate their data into the appropriate cells regardless of what I end up changing the name on the bottom of the tab to. Cell B7 will automatically be the average of G42 across all “TWR 3-Level Master” sheets when added into the workbook, but after the page has been copied from the “master” workbook into the “status” workbook the name will be changed.

How do I do this? Is it possible without using VBA?

[rylo]

Hi

One way would be to add "bounding" sheets. So if you create 2 new sheets in the example workbook, call them TWR Start and TWR End, and put them either side of the existing TWR Test File sheet (so it is Main Page, TWR Start, TWR TEST FILE, TWR End, GSA Test File), then
Main Page!B6: =SUM('TWR Start:TWR END'!G41)/COUNT('TWR Start:TWR END'!G41)

Then create a new sheet, call it twr new one, but in say 50 in G41, then move it immediately before TWR End, you will see that the formula in Main Page B6 will automatically recalculate.

If you do this for each of your type blocks, then you could build formulas that would automatically recalcualte when you add, or delete / move out sheets from between those bounding sheets.

HTH

rylo

[gatornut30]

I think I understand what you are saying. Would there be a way to hide the TWR start and TWR end sheets so the end user will not see them? I will know they are there and serving their purpose but others will not.