Protecting a sheet but NOT opening the source sheet

[NJS1982]

Hi all, so bit of a confusing one.

I have a workbook with one worksheet in it (used by Team A) that is a replica of a worksheet (source sheet) in another workbook that other colleagues use/populate/edit (Team B). It's designed so as to pull through data from a working sheet that Team B are using, for info only so that Team A can see the content when they need to.

I want to lock this tab down, so I protect the sheet, but every time I then click on a cell, it seems to want to open this source data workbook - any ideas how I can stop it from doing this?

 

[sijpie]

How do you make your copy? Try the following:
Right click on the source sheet, copy sheet to new workbook (make sure to tick the copy checkbox).
Now click on the the little field to the right of the A of column A and above the 1 of row 1.
This selects the whole sheet. Now do a normal copy (Ctrl-C) and then Paste as Values.

That does away with all formulas and references to the source workbook.

 

[NJS1982]

Hi, the 'copy' is basically formulas - so, = link to the other sheet and the cell, across the entire sheet.

 

[sijpie]

Yes, then you will get the issue with links to the source file.

But the team A does not need the formulas, so copy all and paste as values does away with the formulas. see above for the method

 

[Vogateer]

Have you tried Excel's Power Query (Get Data) feature for this? Sounds like it might do what you're wanting without the formulas, but you will need to refresh it or set it to refresh automatically.