Validation - Allow selection from list only if another cell isn't blank

[tool_fairy]

Hiya,

To put in a simplified way. I currently have a validation list in column A under the heading 'Status' with three options in the list, "Complete", "On Hold", "Chase". Column B is under the heading 'Resolution'. I need to have it so that it will not allow the user to change the status to "Complete" if B is blank. A message box should also pop up advising of the error.

I feel this shouldn't be too difficult but after searching and trying a few things I've still not been able to work this out.

I've attached a simple example layout.

Cheers

[sandy666]

Try

Please Login or Register  to view this content.

where Status is a list created in Name Manager.
Uncheck "Ignore blank"

or if B2 is empty and you want choose "On hold" or "Chase" try

Please Login or Register  to view this content.

where second is a list created in Name Manage

List Status:
Complete
On hold
Chase

List second:
On hold
Chase

Names up to you

[tool_fairy]

Thank you for your quick reply and your suggestion works well.

The only thing I would say is that if possible, the option for "Complete" would always be there in the list but there would be a message box pop up if they selected it and B2 was blank.

Any idea how I could do this?

[sandy666]

DV has a few limits. If you want something like you said go to VBA forum and start new thread there.

for more:
Apply data validation to cells
More on data validation
Description and examples of data validation in Excel (don't care about Office 2003 )

[tool_fairy]

Ahh, ok. Thank you for help though. I hadn't though of doing that so that'll definitely be useful for the future.