| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Formula that results in Protection????
Hi,
I was wondering if there was a way to write an IF formula that results a range of cells to be protected? In other words, if A1 = 500, then B1:B10 would be protected? I also heard there may be a way to format a cell to where you could have a formula in, yet still be able to key punch in it, without having to erase the formula in the cell. This too would satisfy my needs. Any truth to it? If someone could answer either of my inquiries, I would appreciate it. Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula that results in Protection????
Sorry but No.
A formula can do one thing and one thing only: it can return a value If you have a formula in a cell and you type something else in that cell and press Enter then the formula has gone. To get this to work: "if A1 = 500, then B1:B10 would be protected" you would need to use VBA code best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John Sofillas" wrote in message ... Hi, I was wondering if there was a way to write an IF formula that results a range of cells to be protected? In other words, if A1 = 500, then B1:B10 would be protected? I also heard there may be a way to format a cell to where you could have a formula in, yet still be able to key punch in it, without having to erase the formula in the cell. This too would satisfy my needs. Any truth to it? If someone could answer either of my inquiries, I would appreciate it. Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula that results in Protection????
On Jul 28, 8:34*am, "Bernard Liengme" wrote:
Sorry but No. A formula can do one thing and one thing only: it can return a value If you have a formula in a cell and you type something else in that cell and press Enter then the formula has gone. To get this to work: "if A1 = 500, then B1:B10 would be protected" you would need to use VBA code best wishes -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "John Sofillas" wrote in message ... Hi, I was wondering if there was a way to write an IF formula that results a range of cells to be protected? In other words, if A1 = 500, then B1:B10 would be protected? I also heard there may be a way to format a cell to where you could have a formula in, yet still be able to key punch in it, without having to erase the formula in the cell. This too would satisfy my needs. Any truth to it? If someone could answer either of my inquiries, I would appreciate it. Thanks!- Hide quoted text - - Show quoted text - I was affraid of this. What is VBA code? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula that results in Protection????
VBA: Visual Basic for Applications
David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Search Google with: Excel VBA Tutorial best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John Sofillas" wrote in message ... On Jul 28, 8:34 am, "Bernard Liengme" wrote: Sorry but No. A formula can do one thing and one thing only: it can return a value If you have a formula in a cell and you type something else in that cell and press Enter then the formula has gone. To get this to work: "if A1 = 500, then B1:B10 would be protected" you would need to use VBA code best wishes -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "John Sofillas" wrote in message ... Hi, I was wondering if there was a way to write an IF formula that results a range of cells to be protected? In other words, if A1 = 500, then B1:B10 would be protected? I also heard there may be a way to format a cell to where you could have a formula in, yet still be able to key punch in it, without having to erase the formula in the cell. This too would satisfy my needs. Any truth to it? If someone could answer either of my inquiries, I would appreciate it. Thanks!- Hide quoted text - - Show quoted text - I was affraid of this. What is VBA code? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula that results in Protection????
While you can not protect the cells with a formula you could use custom data
validation to make inputs invalid in cells B1:B10 meaning that they are protected from change. Select the Range B1:B10 Click Data - Validation... -Custom =$A$1<500 Uncheck Ignore Blanks You can change the error alert. Click OK If 500 is entered in Cell A1 then B1:B10 can not be modified. -- HTH... Jim Thomlinson "John Sofillas" wrote: Hi, I was wondering if there was a way to write an IF formula that results a range of cells to be protected? In other words, if A1 = 500, then B1:B10 would be protected? I also heard there may be a way to format a cell to where you could have a formula in, yet still be able to key punch in it, without having to erase the formula in the cell. This too would satisfy my needs. Any truth to it? If someone could answer either of my inquiries, I would appreciate it. Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula that results in Protection????
On Jul 28, 9:46*am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: While you can not protect the cells with a formula you could use custom data validation to make inputs invalid in cells B1:B10 meaning that they are protected from change. Select the Range B1:B10 Click Data - Validation... -Custom =$A$1<500 Uncheck Ignore Blanks You can change the error alert. Click OK If 500 is entered in Cell A1 then B1:B10 can not be modified. -- HTH... Jim Thomlinson "John Sofillas" wrote: Hi, I was wondering if there was a way to write an IF formula that results a range of cells to be protected? In other words, if A1 = 500, then B1:B10 would be protected? I also heard there may be a way to format a cell to where you could have a formula in, yet still be able to key punch in it, without having to erase the formula in the cell. This too would satisfy my needs. Any truth to it? If someone could answer either of my inquiries, I would appreciate it. Thanks!- Hide quoted text - - Show quoted text - THANK YOU!! This is exactly the info I was looking for. However, I am still able to overwrite the fields in the range. Here is exactly what I need to do/what I did. Please advise where I messed up. Thanks! Selected Range Clicked Data - Validation... -Custom Formula =$M$11<=$M$14 Unchecked Ignore Blanks Checked apply these changes to all other cells Typed in my message and subject Did nothing to Error Alert tab ??? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula that results in Protection????
Not knowing exactly what your criteria is it is hard to comment. Note that in
order to allow entry your formula must evaluate to false. When it evaluates to true then the entry is not valid. M11 and M14 need to be of the same data type. That should work. If you are still having difficulty then do some testing with the formulas by hard coding in numbers to see how it works... -- HTH... Jim Thomlinson "John Sofillas" wrote: On Jul 28, 9:46 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: While you can not protect the cells with a formula you could use custom data validation to make inputs invalid in cells B1:B10 meaning that they are protected from change. Select the Range B1:B10 Click Data - Validation... -Custom =$A$1<500 Uncheck Ignore Blanks You can change the error alert. Click OK If 500 is entered in Cell A1 then B1:B10 can not be modified. -- HTH... Jim Thomlinson "John Sofillas" wrote: Hi, I was wondering if there was a way to write an IF formula that results a range of cells to be protected? In other words, if A1 = 500, then B1:B10 would be protected? I also heard there may be a way to format a cell to where you could have a formula in, yet still be able to key punch in it, without having to erase the formula in the cell. This too would satisfy my needs. Any truth to it? If someone could answer either of my inquiries, I would appreciate it. Thanks!- Hide quoted text - - Show quoted text - THANK YOU!! This is exactly the info I was looking for. However, I am still able to overwrite the fields in the range. Here is exactly what I need to do/what I did. Please advise where I messed up. Thanks! Selected Range Clicked Data - Validation... -Custom Formula =$M$11<=$M$14 Unchecked Ignore Blanks Checked apply these changes to all other cells Typed in my message and subject Did nothing to Error Alert tab ??? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Formula that results in Protection????
On Jul 28, 12:56*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: Not knowing exactly what your criteria is it is hard to comment. Note that in order to allow entry your formula must evaluate to false. When it evaluates to true then the entry is not valid. M11 and M14 need to be of the same data type. That should work. If you are still having difficulty then do some testing with the formulas by hard coding in numbers to see how it works.... -- HTH... Jim Thomlinson "John Sofillas" wrote: On Jul 28, 9:46 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: While you can not protect the cells with a formula you could use custom data validation to make inputs invalid in cells B1:B10 meaning that they are protected from change. Select the Range B1:B10 Click Data - Validation... -Custom =$A$1<500 Uncheck Ignore Blanks You can change the error alert. Click OK If 500 is entered in Cell A1 then B1:B10 can not be modified. -- HTH... Jim Thomlinson "John Sofillas" wrote: Hi, I was wondering if there was a way to write an IF formula that results a range of cells to be protected? In other words, if A1 = 500, then B1:B10 would be protected? I also heard there may be a way to format a cell to where you could have a formula in, yet still be able to key punch in it, without having to erase the formula in the cell. This too would satisfy my needs. Any truth to it? If someone could answer either of my inquiries, I would appreciate it. Thanks!- Hide quoted text - - Show quoted text - THANK YOU!! This is exactly the info I was looking for. However, I am still able to overwrite the fields in the range. Here is exactly what I need to do/what I did. Please advise where I messed up. Thanks! Selected Range Clicked Data - Validation... -Custom Formula =$M$11<=$M$14 Unchecked Ignore Blanks Checked apply these changes to all other cells Typed in my message and subject Did nothing to Error Alert tab ???- Hide quoted text - - Show quoted text - OK, I understand now. It all drives from the 1st source being false. I am to get this to work now. It works just about as close to what I am shooting for. Only thing it is missing is you are able to hit the delete button to remove the formula in it. Other than hitting delete, you are not able to enter any data into the range of cells. Does this sound about right? No way of getting around helping someone "accidentally" hitting the delete key? Thanks for you support! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
| cell protection based on a formula | Excel Worksheet Functions | |||
| How do I view formula results intead of formula in excel? | Excel Worksheet Functions | |||
| View formula results instead of formula in 2003 version? | Excel Discussion (Misc queries) | |||
| Modifying a formula to display the results of another formula | Excel Worksheet Functions | |||
Linear Mode
