Formulas getting messed up
Posted by Marcelle on October 17, 2001 1:53 PM
I have a simple spread sheet with data on the left side of the page and formulas on the right side of the page. The data area is unprotected for data entry. The formulas are protected. However, if a user uses copy/paste, my formulas get all screwed up. How can I stop this???
Posted by Qroozn on October 17, 2001 3:14 PM
a formula should only get screwed up if they user is copying and pasting another formula which it is referenced to. is this what is happening?
If they are just changing a cell with a numerical value and no formulae in it then it shouldn't change.
Posted by Marcelle on October 17, 2001 5:20 PM
No. No formula is being touched. Only the data. I will give you an example. The formula in cell Q5 is:
=IF(D5="","^",IF(M5="","^^^",(D5/M5)-1))
If I take the numbers in D5 through M5 and click on "cut" (for cut and paste), the formulas in row 5 become anything from =IF(D16="","^",IF(O5="","^^",(D16/O5)-1)) to =IF(D16="","^",IF(M16="","^^^",(D16/M16)-1)) The references to row 16 now reflects where I moved the data to.
In the meantime, the formulas in row 16 are =IF(#REF!="","^",IF(O16="","^^",(#REF!/O16)-1)) and give all #ref errors.
I am only changing the data in columns D through O and the formulas in columns P through AY are becomming unusable.
The only way to change the data without creating this problem is to copy/paste... then go back and clear the original contents. It's very difficult to get everyone to do this consistently!!! Doesn't make for a user friendly file at all!!
MK
Posted by Qroozn on October 17, 2001 5:31 PM
ok. now i gotcha.
my only suggestion would be a validation list.
but unless the cell is protectd than they will always be able to cut/copy/paste as far as i'm aware.
HOw much info do they data enter?
Posted by Marcelle on October 18, 2001 4:02 AM
This file tracks residents weights in a nursing home. The only data that is being entered is the monthly weights in columns D through O (Jan-Dec). For each months weight, the formulas calculate the percent weight changes over the last 1 month, 3 months and 6 months. (State regs!)
I haven't been able to protect anything. When I tried to protect my formulas it stoped my macros from running. For each months report, the macros hid the formulas in the columns for the months not needing to be printed. (That was going to be my next question, BTW. How to protect my formulas and still run the macros.)
But, back to the current problem: The problem is occuring when a resident is transferred to another room. You can't cut and paste the weights to a new room without messing up the formulas.
Please tell me more about validation. Do you think this is the answer to my problem???
Marcelle
Posted by Qroozn on October 18, 2001 3:05 PM
Validation limits what a user can enter. A validation list produces a list to the user of which they must choose one of those options.
I think the best option would be to put a button on the sheet. you wil need other peoples help to get the code right for it.
But the idea is that to move a resident's details to another room than you can push the button. I will do copy/paste, then go back and delete the details from the old cells.
Posted by Marcelle on October 18, 2001 4:08 PM
Where can I find the directions on how to do that?
MK
