Forum Discussion
Pulling data from different sheets into a summary sheet
How can I pull employee data from different sheets into a live-updating summary sheet?
I have 5 groups of employees — A, B, C, D, and E — with their names listed across 5 separate sheets (one for each group).
I’d like to create a summary sheet that displays all employees and the groups they belong to.
Sometimes employees change groups. Is there a way for the summary sheet to update automatically when those changes happens?
2 Replies
In Microsoft Excel open a new blank sheet.Go to Data Tab - Press Enter.See Get Data in Query Function.A Drop Down menu will Pop Up.Select Source From Folder & select all those Excel files and now select refresh Tab than Load & Save.
Try once.Thats easiest for Dynamic Automated compilation.Thank You.
Yes there are ways but more details might be helpful or a sample sheet without any personal or private information. In concept:
a) I would say and recommend that having a 'master sheet' with all your data input and then pulling subsets (pivot tables or filtered outputs) would be an easier and more efficient approach.
b) you can create a combined output using VSTACK or something similar. For example:
=VSTACK( EXPAND([names-in-A], , 2, "A"), EXPAND([names-in-B], , 2, "B"), EXPAND([names-in-C], , 2, "C"), EXPAND([names-in-D], , 2, "D"), EXPAND([names-in-E], , 2, "E") )
The EXPAND will create a second column with the corresponding group letter while VSTACK will stack all those ranges together.
Alternative, more automated options that could cycle across sheets could be done (e.g. you could use REDUCE to cycle over a range of sheet names and use INDIRECT to create each reference), but if you just have these 5 sheets this is probably easier and more efficient. I would still recommend considering re-doing the structure to have all the data in a master table and then use other sheets to show filtered lists or "reports' accordingly.
