Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move source without altering formula
Here is a strange thing that somebody asked me and I couldn't find an answer.
We have a sheet where there are a lot of formulas and they are in locked protected cells. The user fills in other unprotected cells. Sometimes the user puts in a value and then realizes that it is in the wrong place so they cut and past it into the correct place. When they do this it alters the formula that was using the cell. Example: b2=if(upper(a2)="Y","Yes","No") user puts "Y" into A2 then cuts it and pastes into Sheet2.A1 b2 becomes: b2=if(upper(sheet2!a1)="Y","Yes","No") Is there anyway to keep B2 from being changed? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move source without altering formula
Try one of these:
This formula is robust against row/column insertions. This formula will *always* refer to cell A2. =IF(UPPER(INDIRECT("A2"))="Y","Yes","No") This formula is not robust against row/column insertions but will still refer to cell A2 if A2 is cut and pasted elsewhere. =IF(UPPER(OFFSET(B2,,-1))="Y","Yes","No") Biff "barnabel" wrote in message ... Here is a strange thing that somebody asked me and I couldn't find an answer. We have a sheet where there are a lot of formulas and they are in locked protected cells. The user fills in other unprotected cells. Sometimes the user puts in a value and then realizes that it is in the wrong place so they cut and past it into the correct place. When they do this it alters the formula that was using the cell. Example: b2=if(upper(a2)="Y","Yes","No") user puts "Y" into A2 then cuts it and pastes into Sheet2.A1 b2 becomes: b2=if(upper(sheet2!a1)="Y","Yes","No") Is there anyway to keep B2 from being changed? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move source without altering formula
Just a note:
=if(upper(a2)="y", ... and =if(upper(a2)="Y", ... will both evaluate the same. If don't care about case, you can use: =if(a2="y",... If you really do care about case, check =exact() in excel's help. (This is not a replacement for Biff's answer. Just an addition.) barnabel wrote: Here is a strange thing that somebody asked me and I couldn't find an answer. We have a sheet where there are a lot of formulas and they are in locked protected cells. The user fills in other unprotected cells. Sometimes the user puts in a value and then realizes that it is in the wrong place so they cut and past it into the correct place. When they do this it alters the formula that was using the cell. Example: b2=if(upper(a2)="Y","Yes","No") user puts "Y" into A2 then cuts it and pastes into Sheet2.A1 b2 becomes: b2=if(upper(sheet2!a1)="Y","Yes","No") Is there anyway to keep B2 from being changed? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to move a formula from one sheet to another | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Returned: Copying a formula horizontally, the source data is verti | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |