Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
portected cells
I have created spreadsheets with unprotected areas for data entry. The data
is used in formulas in protected cells. If the user enters data in the wrong cell and moves it to the correct cell, the formulas are changed. Is there any way to prevent the corruption of the formulas by the users action? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
portected cells
cell protection doesn't help you to solve your problem. Maybe you can make
your formulas more robust. Example: Lets assume that your unprotected cells A1 and A2 contain the values 1 and 2. Your protected cells B1 and B2 contain the formulas A1*2 and A2*2. If the user moves the contents of A2 to A1, your formula in B1 will produce an error. However if your formulas in B1 and B2 would read =OFFSET(B1,0,-1)*2 and =OFFSET(B2,0,-1)*2, then moving cells in A1:A2 would still produce correct results since the formulas point to cells positions (which are fixed) rather than cell addresses (which are overwritten by move operations). Cheers, Joerg Mochikun "John Folwell" wrote in message ... I have created spreadsheets with unprotected areas for data entry. The data is used in formulas in protected cells. If the user enters data in the wrong cell and moves it to the correct cell, the formulas are changed. Is there any way to prevent the corruption of the formulas by the users action? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
portected cells
Thank you. That is certainly a viable solution. However in a large complex
spread sheet where the fill function is used extensively to create formulas this would make creating an idiot proof application extremely tedious. Why isnt there another level of protection to keep formulas uncorruptable? I can probably use your idea in a workaround. Thanks again. "Joerg Mochikun" wrote: cell protection doesn't help you to solve your problem. Maybe you can make your formulas more robust. Example: Lets assume that your unprotected cells A1 and A2 contain the values 1 and 2. Your protected cells B1 and B2 contain the formulas A1*2 and A2*2. If the user moves the contents of A2 to A1, your formula in B1 will produce an error. However if your formulas in B1 and B2 would read =OFFSET(B1,0,-1)*2 and =OFFSET(B2,0,-1)*2, then moving cells in A1:A2 would still produce correct results since the formulas point to cells positions (which are fixed) rather than cell addresses (which are overwritten by move operations). Cheers, Joerg Mochikun "John Folwell" wrote in message ... I have created spreadsheets with unprotected areas for data entry. The data is used in formulas in protected cells. If the user enters data in the wrong cell and moves it to the correct cell, the formulas are changed. Is there any way to prevent the corruption of the formulas by the users action? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
portected cells
Idiot-creation far outstrips Idiot-proofing
Gord Dibben MS Excel MVP On Thu, 28 May 2009 09:30:01 -0700, John Folwell wrote: Thank you. That is certainly a viable solution. However in a large complex spread sheet where the fill function is used extensively to create formulas this would make creating an idiot proof application extremely tedious. Why isn’t there another level of protection to keep formulas uncorruptable? I can probably use your idea in a workaround. Thanks again. "Joerg Mochikun" wrote: cell protection doesn't help you to solve your problem. Maybe you can make your formulas more robust. Example: Lets assume that your unprotected cells A1 and A2 contain the values 1 and 2. Your protected cells B1 and B2 contain the formulas A1*2 and A2*2. If the user moves the contents of A2 to A1, your formula in B1 will produce an error. However if your formulas in B1 and B2 would read =OFFSET(B1,0,-1)*2 and =OFFSET(B2,0,-1)*2, then moving cells in A1:A2 would still produce correct results since the formulas point to cells positions (which are fixed) rather than cell addresses (which are overwritten by move operations). Cheers, Joerg Mochikun "John Folwell" wrote in message ... I have created spreadsheets with unprotected areas for data entry. The data is used in formulas in protected cells. If the user enters data in the wrong cell and moves it to the correct cell, the formulas are changed. Is there any way to prevent the corruption of the formulas by the users action? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions |