How to make data in cells mandatory?

[r.kondapalli]

Hi yaa, I am modifying an excel sheet & got stuck with this scenario.
I have a column ‘Age’ which selects the values (1-16, 16-18, 18-60, 60+) from a dropdown.

Age name Smoke abc xyz
0-16 Mandatory Mandatory Mandatory
16-18 Mandatory Mandatory Mandatory
18-60 Mandatory Mandatory
60+ Mandatory Mandatory Mandatory



Now based on the Age field, it should point out that some fields are madatory & the rest are optional.
Eg. Any one who has an 1-16 must fill name, abc & xyz. And Smoke is optional. In the same way Age 16-18 must fill name & smoke. And abc & xyz are optional.

Can you kindly let me know how I can do this please.

Many Thanks

this was posted at http://www.mrexcel.com/forum/showthread.php?t=519625 without any luck..Please help

[Domski]

The best way to achieve this would be to use a VBA Userform that made fields available for completion based on the age range chosen. Some validation could be done when the form is submitted prior to completion of the data in the excel sheet to ensure the correct fields has all been populated.

With about VBA you are really going to be limited to a warning message in a cell for each row based on a complex IF statement.

Dom

[Domski]

I've put together an example of how it can be done with the if statements.

Dom

[Domski]

Note that you should also make folk @ Mr Excel aware that you have cross posted this.

Dom

[r.kondapalli]

Hi Dom, Thanks for your help.

Can I know if there is any way that I can prevent users in saving the excel file if any cell in this sheet is shaded in red?

ie, For eg. If cell k3 is coloured red, & if the user tries to save the file, It should throw a pop-up message "File cant be saved. Please fill ALL cells in Red"

Thanks for your help again

[Domski]

Should be easy enough.

How are cells coloured? Conditional formatting?

Can you uploads a sample workbook.

Dom

[Domski]

Should be easy enough.

How are cells coloured? Conditional formatting?

Can you uploads a sample workbook.

Dom

[r.kondapalli]

Hi Dom, Yes, the cells are colour coded using conditional formatting.
I have attached a sample workbook test.xls

In A1 of sheet1, Data can be selected from drop down menu.
Based on which, I have done some conditional formating to show which cells are mandatory to fill by colour coding the cells in RED.

All I am looking is, If a person selects a data in A1 from the drop down & a couple of cells are highlighted RED(meaning some of the required information is incomplete), he SHOULD NOT be able to save this file(test.xls) & also I want an ERROR pop-up displaying
"Some of the mandatory fields are blank.
Please fill all the fields in RED to save your work"

Thanks
Raj

[r.kondapalli]

Hi Dom or anyone who can help.
Any idea how this can be done?
Thanks