Asked By
jameskerlin
0 points
N/A
Posted on - 08/16/2011
I want to input some conditional data in Microsoft Excel workbook between 33 to 100. But it gives me an error message when I type less than 33 or greater than 100.
Is it possible in Excel? If possible, how can I set this condition?
Answered By
seamus
0 points
N/A
#115626
Conditional data input in Microsoft Excel
Creating conditional input is very simple and easy.
-
First of all you must know how to write a formula in Excel. It is easy just type "=" sign before the required formula. For example, I want to divide A1 by B1 then I'll write =A1/B1 simple.
-
Next you must be familiar with logical functions. In your case it is and function as it needs that both conditions i.e value>33 and value<100 must be true.
-
Now select a cell where you want to place the condition
-
Type =if(and(A1>33,A1<100),"ok","error")
This will do the trick if the user enters value less than 33 or greater than 100 in A1 then it will display error.
Answered By
Trevor
0 points
N/A
#115627
Conditional data input in Microsoft Excel
This should be done only using the VBA macro and assign that VBA macro to a key, so that when pressing the key, it will run the VBA macro. Here’s how:
-
Create a VBA macro to prompt for input and inside the macro you should filter the input based on your condition. You should learn how to create VBA macro before you can do this. It takes 1 week to teach VBA macro here so find some resources and learn. Value should be input in the prompt not on the formula bar.
-
Assign the VBA macro you have created to a key, like for instance CTRL + F2. Pressing CTRL + F2 will run the VBA macro you have created to prompt the user to input a value.
-
On the column you want to filter the input, put a header with the instruction “Press F2 to input a value in this column”.
That’s it! Hope this helps and gives you some idea!
Answered By
zicooo82
0 points
N/A
#115628
Conditional data input in Microsoft Excel
That can be done by using data validation:
1- Select the cell that you want to activate the validation on it for example A1.
2- Select Data tap.
3- Press on data validation button.
4- Select custom from the allow pop-up list of validation criteria.
5- Under formula write the following:
AND(A1>33,A1<100)
Also you follow the instruction of the following image:
