Cell formular

[Glenn]

I have set up a cell to pull infromation via vlookup. It work fine. My
problem is i want to be aboe to change the results of that cell without
loosing the formular for that cell. When i porotect the worksheet it will
not let me do anything with that cell.

 

[Nick B]

Can you give us a little more information?
If you have a vlookup in cell B1 and the first part of the vlookup formula
is looking at A1. If you click on A1 and go to Format->Cells->Protection tab
and uncheck locked. Then go to Tools->Protection->Protect Sheet, you will be
able to change A1. If you change A1, the result in B1 will change as well (if
the result of the vlookup formula is supposed to change).

 

[Glenn]

OK LET ME SEE:
B1 HAS PULLED FROM OTHER CELL VIA VLOOKUP. B1 FORMULAR IS
=VLOOKUP(C1,Z1:Z25,2) THE RESULTS ARE IN B1. THE RESULTS IN B1 IS LET'S SAY
400. IF I WANT TO MANUALLY CHANGE THE RESULTS IN B1 TO SAY 600...WHEN I DO
THIS THE FORMULAR IN B1 IS ERASED. WHEN I PROTECT THE WORKSHEET IT WILL NOT
LET ME CHANGE ANYTHING IN B1. i NEED TO BE ABLE TO MANUALLY CHANGE B1 AND
NOT LOOSE THE VLOOK FORMULAR IN THAT CELL....I HOPE THIS MADE SINCE.

 

[JLatham]

The good news is that your copy of Excel is working properly.

The bad news is that you cannot do what you want in Excel. You can have a
formula that provides a value or you can have a value. Not both at the same
time. Once you type in that 600, the formula is forever gone until you
retype it in.

You might try something similar to this:
move your VLOOKUP() formula out to some other column, lets say X1 for
example's sake.
Then in column A you could have a regular cell with nothing in it to use as
the override value (your 600). Then in B1 you could do something like this:

=IF(A1>0,A1,X1)
so if nothing was in A1 (it could just as easily be Y1) then the results of
your VLOOKUP() formula in X1 show up, but if you put anything at all into A1,
then it replaces the value from X1.

 

[JLatham]

Come to think about it, you could put all of that into B1 like this:

=IF(A1>0,A1,VLOOKUP(C1,Z1:Z25,2))
it would work the same way, and one column saved.
You just need another cell to manually type in the value to override the
lookup results with.

 

[Nick B]

Check out JLatham's reply regarding the formula.

I just wanted to comment on the protection part of your question. By
default, all cells have a "Locked" property turned on. If you protect a
worksheet then all cells that are "Locked" cannot be modified. To make it so
you can change a cell when the worksheet is protected, you must first select
the cell (in this case B1) and go to Format->Cells, click on the Protection
tab, clear out the "Locked" checkbox and click OK. Then when you protect the
worksheet, you will be able to edit the contents of the cell.

See the below link to do the opposite (lock only a few cells rather than
unlock only a few cells):
http://office.microsoft.com/en-au/assistance/HA010548251033.aspx