How do I get one master sheet of names that can be regularly edited and have those names on multiple other sheets?

[685villa]

What I want to do:
I have a workbook with a master sheet of names (40ish people) and I want these names on 30ish other sheets. The master sheet names will occasionally change as company staffing changes.
for the master sheet, I would want just 3 columns of info pulled onto other sheets. However, each sheet will have a rather large set of data in a lot of columns.

Then would I create a master table (on master sheet) with a lot of empty columns and have a VLOOKUP only on the first 3 columns of the master table then have each sheet's table only referencing/looking up the respective first 3 columns from the master sheet?

I still would like to have the ability of filtering each table (via the first 3 columns) on each subsequent table on the other sheets.

I have a lot of example formulas, but they're not compatible with the changing info of the first 3 columns on a master sheet. I am wanting to aleviate the need to edit the first 3 columns of info on each sheet individually, or copy/paste/transpose on the other sheets from the master sheet.

I am looking at creating a single one-stop-shop for all info pertaining to my company since I have A LOT of info to track for each person (ergo the multiple other sheets).

Thanks a lot!

-V

 

[jdellasala]

There are a couple of ways to do this. You can just point to the data in the master workbook such as

=SourceTable.xlsx!MBase[#All]

. That will be what the formula looks like with both Workbooks open, however once you close the source Workbook, the formula will update to include the full path and filename:

='C:\Users\[I]USER[/I]\Desktop\SourceTable.xlsx'!MBase[#All]

The formula will connect to the file and update whatever data you've included in the child Workbook.

The other way is to simply pull the data into the child workbooks with Power Query. Go to Data -> Get Data -> From File -> From Excel Workbook. The first time you do this, select the table needed, then select Transform. Confirm you got the data you needed, and if needed change the data types, and then select Close -> Close & Load To, and load the table to a new worksheet. This will pull in a copy of the table in the Source File. Optionally, once the query (data) is loaded, right click on the Query, select Properties, and check the box for Refresh data when opening the file. If that proves to be inconvenient, simply clicking on Data-> Refresh All will update the data. The code for that will look like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\USER\Desktop\SourceTable.xlsx"), null, true),
    MBase_Table = Source{[Item="MBase",Kind="Table"]}[Data],
    ChangedType = Table.TransformColumnTypes(MBase_Table,{{"Date", type date}, {"Base", Int64.Type}})
in
    ChangedType

The easy way to get it to your other 30 files is to right click on the Query, and select Export Connection File... You will be prompted to save a file in this case Query - MBase.odc. The file will be saved to your local My Data Sources folder, but if you're dealing with 30 other computers, they will all need access to a common Network location. Now on other computers, go to Data -> Existing Connections and click the Browse for more button and load the Query. You'll be prompted for how to load it, you'll probably want to load it to a new worksheet. If you want the query to update on file open, you may have to update the query as before - I'm not sure if those properties are saved with the query.

If I had to do this, I'd go with the power query option - it's more reliable, and a lot faster, and easier. The formula is easier, but will take longer to load and more prone to getting broken unless you protect the workbook. Either way, you have a lot of work having to get it to 30+ computers! Good luck.

 

[Fluff]

That will be what the formula looks like with both Workbooks open, however once you close the source Workbook, the formula will update to include the full path and filename:

Except that it won't work if the sourcebook is closed.

 

[jdellasala]

Except that it won't work if the sourcebook is closed.

I disagree. I used the formula technique extensively against closed workbooks. I would advise against it in this case, particularly if the source is on the network, but it will work.
Of course, that isn't true with Power Query since that's what it's made for!

 

[Fluff]

I used the formula technique extensively against closed workbooks.

Not with structured references you haven't.

 

[jdellasala]

Not with structured references you haven't.

Well, it worked great when I was on a large corporate network using Excel 2019, but it doesn't seem to work now. That leaves Power Query as the best solution.

 

[685villa]

There are a couple of ways to do this. You can just point to the data in the master workbook such as

=SourceTable.xlsx!MBase[#All]

. That will be what the formula looks like with both Workbooks open, however once you close the source Workbook, the formula will update to include the full path and filename:

='C:\Users\[I]USER[/I]\Desktop\SourceTable.xlsx'!MBase[#All]

The formula will connect to the file and update whatever data you've included in the child Workbook.

The other way is to simply pull the data into the child workbooks with Power Query. Go to Data -> Get Data -> From File -> From Excel Workbook. The first time you do this, select the table needed, then select Transform. Confirm you got the data you needed, and if needed change the data types, and then select Close -> Close & Load To, and load the table to a new worksheet. This will pull in a copy of the table in the Source File. Optionally, once the query (data) is loaded, right click on the Query, select Properties, and check the box for Refresh data when opening the file. If that proves to be inconvenient, simply clicking on Data-> Refresh All will update the data. The code for that will look like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\USER\Desktop\SourceTable.xlsx"), null, true),
    MBase_Table = Source{[Item="MBase",Kind="Table"]}[Data],
    ChangedType = Table.TransformColumnTypes(MBase_Table,{{"Date", type date}, {"Base", Int64.Type}})
in
    ChangedType

The easy way to get it to your other 30 files is to right click on the Query, and select Export Connection File... You will be prompted to save a file in this case Query - MBase.odc. The file will be saved to your local My Data Sources folder, but if you're dealing with 30 other computers, they will all need access to a common Network location. Now on other computers, go to Data -> Existing Connections and click the Browse for more button and load the Query. You'll be prompted for how to load it, you'll probably want to load it to a new worksheet. If you want the query to update on file open, you may have to update the query as before - I'm not sure if those properties are saved with the query.

If I had to do this, I'd go with the power query option - it's more reliable, and a lot faster, and easier. The formula is easier, but will take longer to load and more prone to getting broken unless you protect the workbook. Either way, you have a lot of work having to get it to 30+ computers! Good luck.

Many thanks for this!

The product will only be one single workbook, luckily. I may add links to our company Sharepoint for relating info for those cells/tables - but the other 30 tables will be on the same single workbook - just a lot of sheets with the master sheet protected.

 

[jdellasala]

Many thanks for this!

The product will only be one single workbook, luckily. I may add links to our company Sharepoint for relating info for those cells/tables - but the other 30 tables will be on the same single workbook - just a lot of sheets with the master sheet protected.

Sorry, this probably won't work. I used it a couple of years ago using Excel 2019 and on a large corporate network. It doesn't seem to work not. Give it a try, but as @Fluff pointed out, it may not work now.

You're really better off with the Power Query solution. It will obviously take a little more work to set up, but it's a much better solution anyway. And you can always get help here!

 

[685villa]

Sorry, this probably won't work. I used it a couple of years ago using Excel 2019 and on a large corporate network. It doesn't seem to work not. Give it a try, but as @Fluff pointed out, it may not work now.

You're really better off with the Power Query solution. It will obviously take a little more work to set up, but it's a much better solution anyway. And you can always get help here!

I will do more work with it today and will continue to ask questions as I move forward with the product!
Thanks!

 

[jdellasala]

I will do more work with it today and will continue to ask questions as I move forward with the product!
Thanks!

If you have Power Query questions, post to the Power Tools forum, and use XL2BB if posting data.