Auto fill cells in other tabs from one master tab and protect them

[Guest]

I have created a spreadsheet with about 6 tabs. Often data on tab 1worksheet
is found again on tab 2,3,5,6. I would like this data to be auto filled so
that changes only need to be made on the first tab. the change will then be
updated to the other corresponding cells with the new data without having to
enter it onto each tab.
The other tabs will then be protected so the value can only be changed on
the first tab.
Thank you
Martin

 

[Gord Dibben]

Martin

You can link the master cells to the other sheets by selecting a cell on the
master sheet and Copy.

Switch to other sheet and select a cell then Edit>Paste Special>Paste Link.

Once done, you can set the cell properties on other sheets to locked and protect
the sheets.

User cannot edit the cell, but the linked formulas will work.


Gord Dibben MS Excel MVP

 

[Pete_UK]

You can use VLOOKUP for this. Assume the data you want to match is in
column A on all sheets, that you have 6 columns of data, and 100 rows
in Sheet1. Then in your subsidiary sheets you can enter this in B1:

=VLOOKUP($A1,Sheet1!$A$1:$F$100,COLUMN(B1),0)

and it will return the data from column B of Sheet1 corresponding to
an exact match in column A to the value in A1 (the data does not have
to be sorted).

You can then copy this formula across into C1:F1 to get the other
items of data. Then you can copy B1:F1 down the rows as necessary.

Note that if there is not an exact match with data items in Sheet1 the
formula will return #NA, though there are ways of dealing with this.

Hope this helps.

Pete