Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula in Cell changes even if it's locked
Hello,
I've created a spreadsheet to compare numbers with a "IF" statement: =IF(LEN(D2)<1,"Blank",IF(EXACT(D2,$C$1),"Go","No Go")) I've also locked the cells that contain the IF formula. Here's the premise... I enter a number in one cell -- C1 and then I have a row D1 thru D50 to input other numbers. If a number in row D matches with what's in C1, a "Go" appears in row E (where the IF formula resides). If it doesn't match, a "No Go" appears. Issue: Since row D is not locked (so I can enter numbers) I'm able to highlight it. If i highlight and drag it to a different cell, the formula in row E somehow changes (even though it's locked) For example: I click and drag cell D1 to D3 and the IF formula (which is locked) in E3 changed...to the following: Here's how it changed: =IF(LEN(#REF!)<1,"Blank",IF(EXACT(#REF!,$C$1),"Go" ,"No Go")) how is that possible? I even tried adding & within the cells and that didn't work... Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula in Cell changes even if it's locked
By dragging cell D1 to D3, you're replacing D3. The original reference
to D3 in the formula in E3 cannot be resolved, as you've replaced it with another cell. You will also notice that the formula in E1 would refer to cell D3, as that is where you have moved cell D1. "#REF!" means "reference error." If you want to drag the contents of one cell to another without triggering the error, hold down the Control key (CTRL) as you drag. This makes a copy of the cell's contents rather than moving the cell. You can also Copy the cell and Paste into the cell you want to copy the text into. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula in Cell changes even if it's locked
thanks for the quick response. I now understand; however, is there any way
to prevent the formula in E from changing? Password protecting/locking appears not to work. "Mark Lincoln" wrote: By dragging cell D1 to D3, you're replacing D3. The original reference to D3 in the formula in E3 cannot be resolved, as you've replaced it with another cell. You will also notice that the formula in E1 would refer to cell D3, as that is where you have moved cell D1. "#REF!" means "reference error." If you want to drag the contents of one cell to another without triggering the error, hold down the Control key (CTRL) as you drag. This makes a copy of the cell's contents rather than moving the cell. You can also Copy the cell and Paste into the cell you want to copy the text into. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula in Cell changes even if it's locked
The formula doesn't actually change. Rather, the formula cannot be
resolved when you replace one of your cells with another, hence the #REF! notations where you expect cells. You have to keep from moving the cells in column D from one place to another. As I wrote before, if you want to copy text from one cell in the range to another by dragging and dropping, hold down the Control key as you move the cell; this creates a copy of the contents. Otherwise, Copy from the source cell and Paste into the target cell. Note again that when you move, say, D1 to D3, the formula in E1 then refers to D3 instead of D1. So D1 could be blank but E1 could read "Go" or "No Go" depending on the contents of D3. (And of course, E3 will show #REF! because the cell its formula referred to was replaced.) If you move a cell in column D and see a #REF! error, Undo immediately to restore column D to its previous state. This will restore the references and eliminate the error. Then copy as described above. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel |