Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inhibit Cut (allow Copy)
I'm looking for a way to prevent the user from using CUT (including the
equivalent northwest-arrow drag functionality) but not prevent copy/paste or autofill. Users are dragging cells around and destroying associated formulas: Absolute references don't sem to help this. For instance, I have a formula =IF(Z15=0,0,A15/Z15). If the user drags a CUT cell over A15, I get =IF(Z15=0,0,#REF!/Z15). Copying doesn't hurt at all. The cell with this formula resides in, say, A147. I can make all the references absolute - it doesn't help. How can I keep this from happening? duncan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inhibit Cut (allow Copy)
Duncan,
You can use INDIRECT to make your formula immune to the slings and arrows of CUT and drag, etc. =IF(INDIRECT("Z15")=0,0,INDIRECT("A15")/INDIRECT("Z15")) HTH, Bernie MS Excel MVP "duncan" wrote in message ... I'm looking for a way to prevent the user from using CUT (including the equivalent northwest-arrow drag functionality) but not prevent copy/paste or autofill. Users are dragging cells around and destroying associated formulas: Absolute references don't sem to help this. For instance, I have a formula =IF(Z15=0,0,A15/Z15). If the user drags a CUT cell over A15, I get =IF(Z15=0,0,#REF!/Z15). Copying doesn't hurt at all. The cell with this formula resides in, say, A147. I can make all the references absolute - it doesn't help. How can I keep this from happening? duncan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inhibit Cut (allow Copy)
Actually, I tried that, and got some very unexpected results. The cells
containing the new INDIRECT reference now came up with a #REF as a cell Value in even more cases than before. True, the actual formula in the cell remained intact, but the Cell Values seemed to work even worse. I found, for instance, that simply entering a value in the cell in A15 caused the formula'd cell to come up #Ref. When I saw INDIRECT, I thought that it would be the perfect solution, and converted all the cell formulas using a bit of string manipulation. <Buzzer sounds Any ideas why? duncan "Bernie Deitrick" wrote: Duncan, You can use INDIRECT to make your formula immune to the slings and arrows of CUT and drag, etc. =IF(INDIRECT("Z15")=0,0,INDIRECT("A15")/INDIRECT("Z15")) HTH, Bernie MS Excel MVP "duncan" wrote in message ... I'm looking for a way to prevent the user from using CUT (including the equivalent northwest-arrow drag functionality) but not prevent copy/paste or autofill. Users are dragging cells around and destroying associated formulas: Absolute references don't sem to help this. For instance, I have a formula =IF(Z15=0,0,A15/Z15). If the user drags a CUT cell over A15, I get =IF(Z15=0,0,#REF!/Z15). Copying doesn't hurt at all. The cell with this formula resides in, say, A147. I can make all the references absolute - it doesn't help. How can I keep this from happening? duncan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inhibit Cut (allow Copy)
Post some details of the formula and the data, and we might be able to help.
-- HTH RP (remove nothere from the email address if mailing direct) "duncan" wrote in message ... Actually, I tried that, and got some very unexpected results. The cells containing the new INDIRECT reference now came up with a #REF as a cell Value in even more cases than before. True, the actual formula in the cell remained intact, but the Cell Values seemed to work even worse. I found, for instance, that simply entering a value in the cell in A15 caused the formula'd cell to come up #Ref. When I saw INDIRECT, I thought that it would be the perfect solution, and converted all the cell formulas using a bit of string manipulation. <Buzzer sounds Any ideas why? duncan "Bernie Deitrick" wrote: Duncan, You can use INDIRECT to make your formula immune to the slings and arrows of CUT and drag, etc. =IF(INDIRECT("Z15")=0,0,INDIRECT("A15")/INDIRECT("Z15")) HTH, Bernie MS Excel MVP "duncan" wrote in message ... I'm looking for a way to prevent the user from using CUT (including the equivalent northwest-arrow drag functionality) but not prevent copy/paste or autofill. Users are dragging cells around and destroying associated formulas: Absolute references don't sem to help this. For instance, I have a formula =IF(Z15=0,0,A15/Z15). If the user drags a CUT cell over A15, I get =IF(Z15=0,0,#REF!/Z15). Copying doesn't hurt at all. The cell with this formula resides in, say, A147. I can make all the references absolute - it doesn't help. How can I keep this from happening? duncan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inhibit use of the function "Connections" on Excel 2007 | Setting up and Configuration of Excel | |||
inhibit input | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
How copy format, font, color and border without copy/paste? | Excel Programming |