Data Validation in a Different way!

[danish6061]

*This is not an Excel problem, this is Google spreadsheet problem. I am posting my problem here because I have received very helpful solutions on excel through this site, but now I am in a problem with google sheet and I don't have any platform except this, So please help me!

I have a Google spreadsheet that has a list of problems, the resolution to those problems, and the status of completion.
Column G is where the users are to enter the needed resolution to the problem. Column H is where the users indicate the status (with only three options):
* Not started
* In Progress
* Complete

Users should always enter text into column G that explains what the resolution to the problem is. Then they should change the status to indicate it is now either "In Progress" or "Complete".
I have conditional formatting set so the cell in column G is highlighted in bright yellow until something is entered into it, but users are still sometimes changing the status without providing a resolution which I'd like to prevent.

Is there a way to prevent the user from changing the status to "In Progress" or "Complete" until something has been entered in the resolution column. So if they try to change the status without something being entered in Column G (Resolution column), they would receive an error message letting them know they must first enter a resolution before they can change the status.

Is there a way to do that in Google spreadsheet?
Thanks!

 

[jasonb75]

Not sure if this will work in google, but it works in excel.

First create a named range with the 3 options, then use the formula below to return the list to the validation dropdown.

=IF(G2="",G2,list)

When G2 is empty, the dropdown shows the content of G2 (nothing), when G2 is not empty the list is shown in the dropdown.

 

[danish6061]

No, Jason it's not working.

 

[danish6061]

Please anyone solve my problem!!!

 

[jasonb75]

How did you define the named range?
In excel, you have to use a worksheet range, not an array constant.

 

[danish6061]

Sorry Jason, I don't understand "named range"

 

[jasonb75]

You need to enter the 3 options for the list into 3 cells, for example A1:A3, then define a named range called list that refers to =$A$1:$A$3. It can be anywhere, I've just used A1:A3 as an example.

See Name a range of cells - Computer - Docs Editors Help

 

[danish6061]

I am really sorry Jason, but this is not helpful for me!

 

[jasonb75]

It's the only way that you can do what you're asking for.

 

[danish6061]

Brother could you explain me total process in brief step by step from 1st to last?
Please!!!