Best option for updating spreadsheet formula results for multiple workbooks.

[ConfusedMum]

Hi all, I hope that you are all well on this sunny Saturday

Before I start building loads of spreadsheets I would really like your advice please, this is the example scenario:

1 x Company master spreadsheet - unique company ID, company status (registered, declined, withdrawn, no response), company name + other company details.
4 x separate geographical territories spreadsheets - unique company ID, company status, company name + other company details
Multiple geographical area manager spreadsheets - unique company ID, company status + notes area by area manager when contacting the company asking them to join

What would be the easiest/best/most effective way of making the twice weekly updates to the company Master spreadsheet (change of company status) filter down to the sub-spreadsheets, on opening, so that the company status is updated from the Master to the sub-spreadsheets?

At the moment I have used an XLOOKUP similar to this: =xlookup(A2,[Master.xlsx]Data!A:A,[Master.xlsx]Data!B:B,"Not Listed",0) What I need is for this formula to automatically update from the Master spreadsheet when the sub-spreadsheet is opened, so that those opening the spreadsheets don't have to press any buttons, click on anything, etc... - it just does it automatically!

Would I be right in thinking that this has something to do with data connections??

I like learning stuff, so if anyone could point me in the right direction with some tips and maybe online learning suggestions that would be FANTASTIC!

Thank you so much everyone and have a lovely rest of weekend!

 

[awoohaw]

I think Power Query would be your best option. Mr Excel has a forum called Power Tools that may have more solutions for you there.
But, yes, you would build 4 spreadsheets connected to the one Master spreadsheet via power query.
In each of the 4 spreadsheets you would use Power Query to filter the master data by region.

One thing i'm unsure of is if you have to "click refresh" when each workbook is opened or not. I would hope that even so, your teams would not object to that one step.

ExcelIsFun youtube channel has a great video on Power Query, take a look: