PDA

View Full Version : Excel - Data Validation



bk T
21-07-2005, 07:02 PM
In Excel, I would like to perform this data validation in this cell:

If the number <500, I would like it to give a warning message or even sound or flashing colour.

Is it possible at all? Have been playing with the Data/validation function, but not successfull.

Cheers

Parry
22-07-2005, 05:01 AM
Try these settings in Data Validation.

Settings Tab
Allow: Decimal (or whole number if you only want to allow whole numbers)
Data: Greater Than
Minimum = 500
Ignore Blank = Ticked means that if someone amends a value to blank then no error message will appear. Unchecking the box means blank is invalid and a message box will appear if the cell is changed to blank. Leaving the cell as blank without entering anything will not cause an error (ie you cant force a value to be entered using data validation - vba code would be required to achieve this. I could write some code for you if this is needed but I would need some more details such as the cell range involved.)

Error Alert Tab
Title: The title of the message box (eg Invalid Value)
Error Message: The text to describe the error (eg You must enter a value greater than or equal to 500)

bk T
22-07-2005, 07:26 PM
Try these settings in Data Validation.

Settings Tab
Allow: Decimal (or whole number if you only want to allow whole numbers)
Data: Greater Than
Minimum = 500
Ignore Blank = Ticked means that if someone amends a value to blank then no error message will appear. Unchecking the box means blank is invalid and a message box will appear if the cell is changed to blank. Leaving the cell as blank without entering anything will not cause an error (ie you cant force a value to be entered using data validation - vba code would be required to achieve this. I could write some code for you if this is needed but I would need some more details such as the cell range involved.)

Error Alert Tab
Title: The title of the message box (eg Invalid Value)
Error Message: The text to describe the error (eg You must enter a value greater than or equal to 500)


Thanks, Parry. I tried your above suggestion but it doesn't work.

I'm actually creating a simple stock inventory control spreadsheet and would like it to give a warning when my stock has drop to a certain level. For example, on 22/7/05, I withdraw 200 units from the shelve of 650 units and now the stock level has dropped to 450 units. If I can create something for this Excel spreadsheet to trigger a warning when the stock level drops to say, 500, will achieve my goal.

Cheers

Parry
22-07-2005, 08:43 PM
Data Validation is for when data is entered into a cell, not as a result of a formula calculation which appears to be the case for you.

You can use conditional formatting to highlight a cell when its outside your boundaries but to produce a message would require code. If its a message then you will need to advise which cells has the stock totals and which cells contain the item name.

bk T
22-07-2005, 09:43 PM
....
...You can use conditional formatting to highlight a cell when its outside your boundaries but to produce a message would require code. If its a message then you will need to advise which cells has the stock totals and which cells contain the item name.

What about making it changes colour, say from black to red?

Where can I get more information and learn how to use conditional formatting?

I'm not prepared to buy an expensive book on Excel due to financial reasons.

Cheers

Parry
23-07-2005, 04:05 AM
What about making it changes colour, say from black to red?

Where can I get more information and learn how to use conditional formatting?

I'm not prepared to buy an expensive book on Excel due to financial reasons.

Cheers

You dont books - its all described in the Help.
1. Select the cells that you want to change colour
2. Select Format|Conditional Formatting from the menu
3. Have these options in Condition 1:
1st dropdown = CellValueIs
2nd Dropdown = Less than
Box = 500
4. Click the Format button and then the patterns tab and choose a colour. Click OK twice and your all done

bk T
23-07-2005, 10:55 AM
Good Morning Pary, you've been very helpfull.

The conditional formatting works although there is no error message. But I think it is good enough for the job.

Thanks, once again.

Cheers & have a great week-end

Graham L
23-07-2005, 03:00 PM
... "although there is no error message".

Surely it's not an error to have moved some stock. "Whoopee, I've made another sale". You would hope to have it as the normal case. And the computer is just reminding you order some more. :cool:

bk T
23-07-2005, 06:48 PM
... "although there is no error message".

Surely it's not an error to have moved some stock. "Whoopee, I've made another sale". You would hope to have it as the normal case. And the computer is just reminding you order some more. :cool:

Graham, sorry, "error message" is certainly the wrong term to use here; a "reminder message" is probably the more appropriate term to use.

Thanks for pointing out, anyway.

Cheers