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

[chris4]

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).

 

[Toadstool]

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.

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)

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

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

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

 

[mumps]

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]

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]

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
	A	B	C
1	Named Field	Contents of Named Field
2	New Dependant	New Dependant
3	First Name	First Name
4	Middle Name	Middle Initial
5	Surname	Surname
6	DoB	DoB
7	Drivers License	Drivers License
8	Employee	Employee
9	Child	Ankle Biter
10	Request for New Vehicle	Request for New Vehicle
11	New Hehicle Checkmark	Y	N
12	Drivers License	Drivers License
13	Reason	Reason
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
	F	G	H	I	J
2	Request for New Vehicle			Y
3
4
5
6	First Name	Middle Initial	Surname		Drivers License
7	Jim	James	Watson		D123456
8
9	Reason	Maintenance advise cost of repair is not worth it. Ref X29485
10
11
12
13
New Vehicle

Cell Formulas
Range	Formula
G6	G6	=Middle_Name

Named Ranges
Name	Refers To	Cells
Middle_Name	=Master!$B$4	G6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G9:I13	Expression	=($I$2="Y")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=New_Vehicle_Checkmark

 

[chris4]

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
	A	B	C
1	Named Field	Contents of Named Field
2	New Dependant	New Dependant
3	First Name	First Name
4	Middle Name	Middle Initial
5	Surname	Surname
6	DoB	DoB
7	Drivers License	Drivers License
8	Employee	Employee
9	Child	Ankle Biter
10	Request for New Vehicle	Request for New Vehicle
11	New Hehicle Checkmark	Y	N
12	Drivers License	Drivers License
13	Reason	Reason
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
	F	G	H	I	J
2	Request for New Vehicle			Y
3
4
5
6	First Name	Middle Initial	Surname		Drivers License
7	Jim	James	Watson		D123456
8
9	Reason	Maintenance advise cost of repair is not worth it. Ref X29485
10
11
12
13
New Vehicle

Cell Formulas
Range	Formula
G6	G6	=Middle_Name

Named Ranges
Name	Refers To	Cells
Middle_Name	=Master!$B$4	G6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G9:I13	Expression	=($I$2="Y")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=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!

 

[chris4]

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