Worksheet change event_event on based on null value

[punnu07]

Hi,

I have the following 4 columns in an excel file. In column Status I have different drop down values like Completed pending, to review etc.

Now my problem is if final review issued date is blank, then user shouldn't be able to select the status "Completed" from drop down in status column and a error message should pop up.

Final Review Issued

Risk Engineer

Underwriter

Status

<tbody>
</tbody>

Thanks in advance.

 

[Sunny Kow]

No files are allowed to be posted in this forum. What Takae and I are trying to do is to change the validation so that it will not show Competed in the validation list.
You can try using this formula in your validation in column O like this

IF($L$1<>"",$S$1:$S$3,$S$2:$S$3)

where S1:S3 contains your validation list Completed,Pending and Review respectively.

Change to suit your needs.

 

[punnu07]

I don't know what is happening but validation is not picking up both list together means if I entered date in column L, then i am able to see the list for all options but as soon as i delete that date from the cell, i am not getting list for that. I have created 2 lists one for if date is entered and one for if date is not entered. Am i doing something wrong?

 

[Sunny Kow]

From your description, there don't seem to be anything wrong.
Did you select the range in your validation correctly?
What formula did you use? Try removing the $ sign from the L1
IF(L1<>"",$S$1:$S$3,$S$2:$S$3)

 

[punnu07]

Got It. Some VBA coding done on the column was not letting the things to happended. Now it is working. Thank you Sunny for your help. Thank you very much

 

[Sunny Kow]

Happy to know you have resolve your issues.

Cheers
Sunny