Repost: Locking data in selected cells

  • Thread starter Thread starter Andy Sandford
  • Start date Start date
A

Andy Sandford

Hi,

Sorry to post this again people, but I just can't figure this one out!! 8o((

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(s) 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
 
There's no magic answer for this -- it depends on the
sheet constructs and whether or not you ever want them to
update after some point. The links are there specifically
to do the update you don't want. Once you've looked them
over and decided they don't need to be updated again, you
could simply select the three cells and copy->paste
special values only either over the top of them (i.e. no
more link), underneath, next to ... whatever.

Too little is known about what you're really trying to do
to make a clean stab at it.

Excel Support Technician
www.canhelpyou.com
 
Kent,

Bear with me, this takes some explaining!!

I have a workbook which holds price info on air conditioning equipment. The
user selects how much cooling is required, then a table is populated with up
to 10 suitable systems.

There is a separate workbook referring to each of the customers, each sheet
of which details the cost for installation of the equipment. Each of these
sheets refers to a single office in a building, which may have many offices.

What I do is this :

1) open the relevant sheet in the customer workbook (say Office 1)

2) open the price info workbook via a hyperlink, input the cooling required
and populate the table, each entry of which has a reference number.

3) Return to the customer workbook and enter that reference number in a cell
in Office 1, which then uses vlookup to enter the system data in four
further cells.

I then open the next sheet (say Office 2) and repeat the process...

The problem I have is that obviously the info for Office 1, retrieved by
vlookup will change when I change the data in the price info workbook to
suit Office 2.

I need to lock the info in Office 1 once it has been selected by the
reference number.

I thought I'd be able to do this via a check box, that once checked would
prevent the data from being updated (i.e. disable vlookup), but still
display the original info. Not So!!

Ideally, if the box was subsequently unchecked, vlookup could once again
populate the cells with the new data.

Thanks in advance for your insight!!

Andy
 
Back
Top