How Do I automatically populate one sheet with the contents of 6 other sheets in the workbook?

[BeeBear2]

Hi,

I have a Training Workbook that has one tab for the "Master" list, and one tab for each of 6 Divisions. What I want to do is make the Master Tab automatically populate with the rows of information from each of the Division tabs, to create the master list.

Each of the division tabs will contain exactly the same columns and data types, but different individual data.

For example:

DATE / TRAINER / TYPE OF TRAINING / LENGTH OF TRAINING (HRS) / NUMBER OF ATTENDEES
05/09/2016 / JOHN SMITH / MS ACCESS / 4 / 3

The same columns are in the master tab, with one additional column of Division, to differentiate once the information is transferred to there.


I can't just split the master sheet into having the first 5 rows as Department 1, 2nd 5 as Department 2 etc, because there is an undetermined number of training events for each department, and I dont want to have to do a copy across from the tabs to the main sheet every month.

I'm thinking maybe a nested VLoookup formula, but I'm not sure how to build it. I have a reasonable XL knowledge, but havent done much with VLOOKUPS.

Any help would be greatly appreciated.

Thanks

 

[combo]

You can use divisions sheets as source data for the query that returns aggregation to the Master sheet. In this solution, without macros, "automatically" means that you can refrash data on demand, right-clicking the range and selecting "refresh" command. Depending on your excel version you can use power query add-in or this feature built-in in excel 2016 - Data > Get&Transform in ribbon.
The steps:
1) if you haven't done this yet, convert each division data into structured tables (Insert > Table) and give them meaningful names,
2) select first division table, from Get&Transform select "Table", you should get the table in query editor,
3) in the query pane (on the left, expand inf necessary), right-click the first query, duplicate,
4) select new query, on the query settings (on the right) select "Source" (the first line), change division table name to the next division, you may also chnge query name,
5) repeat steps 3-4 for all divisions,
6) select first query, from the menu Combine>Append query add queries for other departments,
7) return data (as table) to excel.


combo

 

[BeeBear2]

Thanks combo,

Seems I have to get the helpdesk to install the Power Query add-on, so as soon as they have done that for me I'll give this a go.
I had already created tables for each of the divisions, just wasn't sure of the next step.

I'll get back to you if I have any questions

 

[BeeBear2]

Ok, so I have now got Power Query installed on my machine, and your steps don't match to this,

Maybe I should point out that I'm using MS Office 2010 (Excel Version 14.0.71). I don't have the "Get&Transform Function that you are talking about.

Can you give me the step-by-step through Power Query tool instead?
Also, which sheet should I be in to get this done?

I did manage to create a query running across all the sheets (and suppressed the 'null' values since my table has some null rows for future filling, but it doesn't automatically update when I add new rows of information.

Power Query - From File (pick the Excel File).
From Navigator - I click the select multiples, and pick all 5 division table names
then click Load. This makes 5 more sheets (sheet 1-5),
Then I create an Append query, and pick all 5 queries and build a table in the Master spreadsheet.

Problem is that regardless of whether I add values or remove values, nothing changes. Even after I've hit Refresh.

Hope you can help because this part is not my area of expertise.

Thanks

 

[combo]

I use excel 2016, so the interface is slightly different. The ms power query info:

https://support.office.com/en-us/ar...e1f-bad5-89f6269cd605?ui=en-US&rs=en-US&ad=US

As I wrote, without macros you will not get automatic updating. The best you can have is updating on demand.
It's important to have source data defined in structured tables. In this case adding new row added in any division sheet automatically resizes the table (input data), you should see this in formatting. In case of deleting entire rows have to be deleted.

You don't need to output divisions queries to worksheets, if they created new sheets, they can be deleted, except of first (output with aggregation). The other queries exist only in structure and their result will be appended to the first one.

If you have some experience with older excel ms query or databases, you can notice that power query is a little different, it has some ideas of data mining tools. Pover query can store more than one query that can be combined in various ways, the query consists of series of actions, each action can be viewed and renamed (in advanced view).

combo

 

[combo]

Again, tested in excel 2016:
I created three structured tables, with identical columns "aa" (text) and "bb" (values). Named them T_1, T_2 and T_3. The queries (syntax visible in advanced editor, type transformation automatic):
1) query Query_1, link only (Query_2 and Query_3 are identical except of source table):
[pre]let
Source = Excel.CurrentWorkbook(){[Name="T_1"]}[Content],
ChangeType = Table.TransformColumnTypes(Source,{{"aa", type text}, {"bb", Int64.Type}})
in
ChangeType[/pre]
2) query Output, to workbook:
[pre]let
Source = Query_1,
#"Append_T2" = Table.Combine({Source, Query_2}),
#"Append_T3" = Table.Combine({Append_T2, Query_3})
in
#"Append_T3"[/pre]
The query output in workbook is a structured table, it is updated on demand.



combo