Conditional data input in Microsoft Excel

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?

• Status: Open
• Question Views: 1675
• Answer Count: 3
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by seamus

Go To Solution

Best Answer

Best Answer

Answered By seamus 0 points N/A #115626

Conditional data input in Microsoft Excel

Creating conditional input is very simple and easy.

1. 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.
2. 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.
3. Now select a cell where you want to place the condition
4. 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.

About seamus

Questions
2

Answers
13

Best Answers
9

• Vote Up 0 Vote Down

• Posted on - 08/16/2011
• Question Category: MS Excel

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:

1. 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.
2. 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.
3. 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!

About Trevor

Questions
2

Answers
315

Best Answers
83

• Vote Up 0 Vote Down

• Posted on - 08/17/2011
• Question Category: MS Excel

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:

About zicooo82

Questions
0

Answers
11

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 08/17/2011
• Question Category: MS Excel