Data Validation in a Different way!

danish6061

New Member
Joined
Mar 16, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
*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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,701
Office Version
  1. 365
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,701
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

danish6061

New Member
Joined
Mar 16, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Sorry Jason, I don't understand "named range"
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,701
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 16, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
I am really sorry Jason, but this is not helpful for me!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,701
Office Version
  1. 365
Platform
  1. Windows
It's the only way that you can do what you're asking for.
 

danish6061

New Member
Joined
Mar 16, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Brother could you explain me total process in brief step by step from 1st to last?
Please!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,441
Messages
5,642,133
Members
417,258
Latest member
amk1979

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top