Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel Data Validation Help!

Jul 7, 2010 7:36AM PDT

Hi,

I have a spreadsheet whereby I want to limit the options for user input in a cell (list). AND I also want to run a check to make sure that the option selected meets certain criteria. So here is my dilemma: How do I allow a list and an if function to operate simultaneously using data validation?

Data: List = 35% or 70%, now here is my criteria for the 70% option: =IF(AND(E6>2.99,E6<9.99,E6<(D6*0.Cool),70%,35%)

So if someone chooses 70% but doesn't meet the requirements I want it to report an error msg.

Help! Happy Thanks much!

Discussion is locked

- Collapse -
Re: data validation
Jul 7, 2010 11:14PM PDT

Why let the user enter this percentage if you already know what it should be?. Just calculate it with this formula and set the cell to read-only.

Am I missing something obvious or didn't you tell the whole story?

Kees

- Collapse -
Re: data validation
Jul 8, 2010 1:44AM PDT

The user has the option to take 35% even if these criteria are met... I know it sounds silly and no one should ever opt for this route, however I'm just a lowly peon and am following orders...

Thanks for your reply Kees and no nothing obvious has been missed.

- Collapse -
You're quite right about the cells.
Jul 8, 2010 6:28AM PDT

You can only do one thing in a cell. So the thing to do:
- put the cell with a drop down list in, say, column E
- put the cell with the error or warning message in a protected column next to it, in column F. You could do conditional formatting for color; so it's green OK, yellow warning and red error.

Kees