UKC

Bit obscure. I have the same table layouto 3 sheets with different data. Without macros or formulas (lookup for example) can I get excel to present the combined data on another sheet. Ideally it would be 3 files, that way other people can edit those in their groups and I can view the master list of the 3 tables.

If I can't do it without macros then I'll at least use 3 files without and just use macros on the combining sheet but it's last resort as anyone blocking macros will not get the right data.

Formula or macros are the logical route.

Why not formulas since you can use external reference links to tie the three files together.

For macros add a last updated option and a button to refresh and then they would have a clue whether it was up to date.

The only solution I can think of that would avoid macros or formulae would be to have one workbook, potentially stored in the cloud (eg: OneDrive / Sharepoint), and use the co-authoring features of Microsoft365 (formerly Office365) to let the groups simultaneously edit it.

https://support.office.com/en-us/article/collaborate-on-excel-workbooks-at-...

I already use that, but I want the collaborative documents to be 1 per team and the combined document won't be shared with them. Looks like macro is the only options.

You could just paste link from the different tables to the master? Then you just have to update the links when you open the master? 

How will the other teams be accessing the 'collaborative documents' and where are they stored?

To check my understanding is right: You have 3 different sheets and you would like to combine the data in these sheets to make 1 summary sheet, without the use of macros or lookups. Each of the 3 sheets is edited by a separate team. 

If this is the case, and the files are stored on a shared drive, you could use Power Query. (edit - you could also use it if they're not on a shared drive to be clear)

Mike

Can you clarify whether you really meant the "without formulas" part? Because I can understand your reasoning for avoiding macros but I don't think it's likely that formulas would cause any issues, if I've understood the problem correctly. And it makes a huge difference. Solving the problem with formulas is probably trivial.

Ideally 3 files, but I could make 3 tabs work (loses some of the privacy).

The 3 files are variable length tables with same headers. All on SharePoint sites within Teams.

If macros then I'll use 3 files and have only a macro file.

By avoiding formula, I meant lookup, I don't want the accuracy of the merge to be affected by how many rows have the right formula.

I'll look up power query. Essentially the difficulty is not the task, it's knowing what functionality to look up as most searches find detail on merging tables in ways that won't work for me.

Thanks. Power query (or just Query now) is the way to go. I've not looked to see if it will work across multiple files, but if not I can separately pull the files into hidden tabs and then combine.

It will work across multiple files, for sure, without needing to hide the sheets. For me this is one of the big advantages of PQ as it keeps file sizes small when working with lots of data. 

You will need to ‘get’ the various tables as ‘connections’, and then use ‘merged queries’ to combine and expand the data into one table. Plenty of info online, but feel free to send me a message if you get stuck!

 Confirm power query is the way to go.  Get M is for data monkey. Well worth it.

I'll be fine now I've found power query. I've done the data connections before when using Excel linked with a SQL server.

Now the next trick is pulling part of the data from the output of a Redmine query so I don't even need to do my updates to the file.

I might start posting here instead of stack overflow