Preventing linked worksheets from updating automatically

[Andy Sandford]

Hi all,

I have a workbook comprised of several essentially identical worksheets.
Each of the worksheets refers to a different room within a building, and are
largely populated manually.

However, there are three cells in each worksheet of the workbook that
automatically reference data from a separate workbook via VLOOKUP - the
problem I have is this...

The other workbook acts as a database and is used as a selection tool to
pick equipment from a list for each room based on various criteria. It is
used repeatedly for each of the worksheets in my "problem" workbook and is
thus constantly changing.

I don't want the values in the three cells on each sheet to change
automatically when data in the other workbook changes.

I need to manually interrogate the values in the other workbook once, and
once only.

Would it be possible to do this via an option button, that once depressed
locks the retrieved values and prevents them from changing?

I'm going quietly crazy with this one!!

Regards

Andy

 

[Guest]

I think you should have a check-box that would have assigned a macro working the following way
- when checked puts formulas (refering the workbooks you wish) in the offending cell
- when unchecked breaks the links (keeps only the values and the cells have no longer formulas

That is if you don't wish to set all the links to update manually and not when opening the workbook - see Edit->Links, the "Startup Prompt" button.

 

[Andy Sandford]

BEDE,

Thanks for your help, but can anyone be more specific i.e how do I go about
recording a macro to achieve this?

Thanks

Andy

I think you should have a check-box that would have assigned a macro working the following way:
- when checked puts formulas (refering the workbooks you wish) in the offending cells
- when unchecked breaks the links (keeps only the values and the cells have no longer formulas)

That is if you don't wish to set all the links to update manually and not

when opening the workbook - see Edit->Links, the "Startup Prompt" button.

 

[Guest]

You can solve it by the followin
Go to optio
1 - open calculation, then clear update remote reference, click save external link valu
2 - open edit click ask to update automatic links
press o
every time you will open the worksheet it will ask you do you want to update the linked sheet press no
if you have office 2003 you can break the link
thanks and by


----- Andy Sandford wrote: ----

Hi all

I have a workbook comprised of several essentially identical worksheets
Each of the worksheets refers to a different room within a building, and ar
largely populated manually

However, there are three cells in each worksheet of the workbook tha
automatically reference data from a separate workbook via VLOOKUP - th
problem I have is this..

The other workbook acts as a database and is used as a selection tool t
pick equipment from a list for each room based on various criteria. It i
used repeatedly for each of the worksheets in my "problem" workbook and i
thus constantly changing

I don't want the values in the three cells on each sheet to chang
automatically when data in the other workbook changes

I need to manually interrogate the values in the other workbook once, an
once only

Would it be possible to do this via an option button, that once depresse
locks the retrieved values and prevents them from changing

I'm going quietly crazy with this one!

Regard

And

 

[Andy Sandford]

Hii
I tried that, but my workbook still updates if I modify the other one - the
problem is that I need them both open simultaneously.

Thanks anyway

Andy

You can solve it by the following
Go to option
1 - open calculation, then clear update remote reference, click save external link value
2 - open edit click ask to update automatic links.
press ok
every time you will open the worksheet it will ask you do you want to

update the linked sheet press no.