URGENT: How to prevent data in cells from changing?

[vinay26]

I really need help here. Appreciate any comments. Preserntly, I have an excel workbook containing a lot of worksheets (tabs). a few are linked to a central server and the information is updated automatically. the other sheets currently need to be manually updated cos the data in the automatically updated spreadsheet is only kept for a few days, in the sense that the old values are removed and the new ones are added. However, in the other sheets that have are not linked to the server, I have to manually copy and paste the new data. I am unable to link the sheets together (using = to the other cell in the automatically updated sheet) since the old data is removed and the value reflected in the linked cell will be 0. How can I stop this? What I want to do is that once the value is updated in the server-linked spreadsheet, it shows up in the non-server linked spreadsheet (via the = function). However, I want the data to remain unchanged (not changed to 0) in the non-server-linked spreadsheet even once the data from the server-linked spreadsheet is removed. Something like a one-change then lock kind of theory.

Anyone knows what can be done? Any function in excel that can do this? Sorry for the long story, I couldn't think of any way to explain the problem.

I really need a solution (if any) to this problem fast. Thanks for all the help.

[patrickcairns]

Although I am not a pro...

One way of solving this is if the location of the linked cells do not change, and there are frequent updates to the server sheet is to code it something like this

Sub StaticSave
Sheets("NON-SERVER PAGE").RANGE("A1").FORMULA = "=SHEETS("SERVER PAGE").RANGE("<LINKED CELL>")"
Sheets("NON-SERVER PAGE").RANGE("A1").COPY
Sheets("NON-SERVER PAGE").RANGE("A1").PASTESPECIAL xlValues
End Sub

and repeat the code for each linked range. I am sure there is a better solution, but this would work and allow you to update it when the server sheet is updated.