Create master worksheet that will update other worksheets when changes are made

chris4

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have several Excel worksheets in a workbook that each contain a form for various requests made by employees. The forms all have the same general layout and very similar information but have some variance in information (for example: two forms may have a cell for 'valid drivers license' but the rest don't). I would like to create a 'master' sheet that contains all of the possible fields(cells) that any of the other sheets(forms) could possibly contain that can be adjusted when needed (change a word or link). I want the changes made in a cell on the master sheet to update the corresponding cell in all of the other sheets. For example, if I change the cell(field) 'middle name' to 'middle initial' on the master sheet I want Excel to make that change to the 'middle name' cell of each sheet automatically (if it contains that cell).
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows
Hi Chris4,

One thing you could use is a Named cell but the setup will take a while.

On your Master sheet in column A make a list of the current titles for the fields which may change and copy to column B.
1611239943881.png


Now select cells in both columns and use Formulas, Create from Selection using the left column to generate names (and if it asks to use duplicates click Yes)
1611240025776.png


Now Formulas, Name Manager should show the list of generated Names, just make sure they all have Scope=Workbook
1611240122543.png


Now go through each sheet and use Formulas, Use in formula to change each cell to use its Name
1611240202906.png


Now if you change the entry on the Master sheet you'll see it changes all the Named cells
1611240291689.png
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your Master sheet which contains all the possible fields. Better still, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

chris4

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Chris4,

One thing you could use is a Named cell but the setup will take a while.

On your Master sheet in column A make a list of the current titles for the fields which may change and copy to column B.
View attachment 30261

Now select cells in both columns and use Formulas, Create from Selection using the left column to generate names (and if it asks to use duplicates click Yes)
View attachment 30262

Now Formulas, Name Manager should show the list of generated Names, just make sure they all have Scope=Workbook
View attachment 30263

Now go through each sheet and use Formulas, Use in formula to change each cell to use its Name
View attachment 30264

Now if you change the entry on the Master sheet you'll see it changes all the Named cells
View attachment 30265
This worked great, (although a tad tedious to find the unique entries in all the worksheets). Thanks for the help! Could I use the same technique for cells that have entries besides values/text. For example, some cells are filled in gey because they don't need to be filled or some have a drop-down arrow to check off as complete. -Using your example from above, the cell beside 'Request for New Vehicle' would have a drop-down to insert a checkmark or the cell beside 'reason' is filled grey. Could a set this up essentially the same way as previously? thanks:)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Originally you'd said you had a template per sheet so I'm not sure why you need to checkmark against New Vehicle, and a tick mark is tricky as you need to start formatting cells as Wingdings but if you can live with blank/X or Y/N then yes.

So here I've added a row and used Formulas, Create from selection using the left column as the name to generate New_Vehicle_Checkmark

Chris4.xlsx
ABC
1Named FieldContents of Named Field
2New DependantNew Dependant
3First NameFirst Name
4Middle NameMiddle Initial
5SurnameSurname
6DoBDoB
7Drivers LicenseDrivers License
8EmployeeEmployee
9ChildAnkle Biter
10Request for New VehicleRequest for New Vehicle
11New Hehicle CheckmarkYN
12Drivers LicenseDrivers License
13ReasonReason
Master


Now if I use Data validation, List and Formulas, Use in Formulas selecting that New_Vehicle_Checkmark it will give me a dropdown with Y or N. If I want to change the options then I just modify the Master

Chris4.xlsx
FGHIJ
2Request for New VehicleY
3
4
5
6First NameMiddle InitialSurnameDrivers License
7JimJamesWatsonD123456
8
9ReasonMaintenance advise cost of repair is not worth it. Ref X29485
10
11
12
13
New Vehicle
Cell Formulas
RangeFormula
G6G6=Middle_Name
Named Ranges
NameRefers ToCells
Middle_Name=Master!$B$4G6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G9:I13Expression=($I$2="Y")textNO
Cells with Data Validation
CellAllowCriteria
I2List=New_Vehicle_Checkmark
 

chris4

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have several Excel worksheets in a workbook that each contain a form for various requests made by employees. The forms all have the same general layout and very similar information but have some variance in information (for example: two forms may have a cell for 'valid drivers license' but the rest don't). I would like to create a 'master' sheet that contains all of the possible fields(cells) that any of the other sheets(forms) could possibly contain that can be adjusted when needed (change a word or link). I want the changes made in a cell on the master sheet to update the corresponding cell in all of the other sheets. For example, if I change the cell(field) 'middle name' to 'middle initial' on the master sheet I want Excel to make that change to the 'middle name' cell of each sheet automatically (if it contains that cell).
Originally you'd said you had a template per sheet so I'm not sure why you need to checkmark against New Vehicle, and a tick mark is tricky as you need to start formatting cells as Wingdings but if you can live with blank/X or Y/N then yes.

So here I've added a row and used Formulas, Create from selection using the left column as the name to generate New_Vehicle_Checkmark

Chris4.xlsx
ABC
1Named FieldContents of Named Field
2New DependantNew Dependant
3First NameFirst Name
4Middle NameMiddle Initial
5SurnameSurname
6DoBDoB
7Drivers LicenseDrivers License
8EmployeeEmployee
9ChildAnkle Biter
10Request for New VehicleRequest for New Vehicle
11New Hehicle CheckmarkYN
12Drivers LicenseDrivers License
13ReasonReason
Master


Now if I use Data validation, List and Formulas, Use in Formulas selecting that New_Vehicle_Checkmark it will give me a dropdown with Y or N. If I want to change the options then I just modify the Master

Chris4.xlsx
FGHIJ
2Request for New VehicleY
3
4
5
6First NameMiddle InitialSurnameDrivers License
7JimJamesWatsonD123456
8
9ReasonMaintenance advise cost of repair is not worth it. Ref X29485
10
11
12
13
New Vehicle
Cell Formulas
RangeFormula
G6G6=Middle_Name
Named Ranges
NameRefers ToCells
Middle_Name=Master!$B$4G6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G9:I13Expression=($I$2="Y")textNO
Cells with Data Validation
CellAllowCriteria
I2List=New_Vehicle_Checkmark

The workbook I have been provided contains 20 sheets already formatted like this (text changed for sample) that I have been tasked to create this Master sheet for ease of use. Maybe this paints a better picture for you - I will go ahead and try your recommendation, THANKS A MILLION!
Picture1.png
Picture3.png
 

chris4

New Member
Joined
Jan 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
The workbook I have been provided contains 20 sheets already formatted like this (text changed for sample) that I have been tasked to create this Master sheet for ease of use. Maybe this paints a better picture for you - I will go ahead and try your recommendation, THANKS A MILLION!
View attachment 31163
Picture2.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top