Questions tagged [data-validation]

Use when the topic is the process of cleaning, correcting and filtering input to reach a state suitable for the operation of a program.

Filter by
Sorted by
Tagged with
0 votes
1 answer
16 views

How can I make a data validation list with easy edition in Google Sheets?

I would like to make a structure similar with a data validation list in Google Sheet so the others users could contribute to it easily. For example, when I use the sheet I select one data validation ...
user avatar
  • 103
0 votes
1 answer
29 views

How do I keep exact text formatting when using a data validation dropdown list in Google Sheets?

I have a column that contains a bold number and a non-bold text part. I want to use this information as a drop-down selection list, but it currently isn't letting me keep the bold formatting on only ...
user avatar
0 votes
2 answers
27 views

Set cell to reject wrongly formatted data

I’ve got a “time tracker” Google Sheet where people need to enter a record of a start time and end time; we then have a formula which works out the duration between the two times. The formula is ...
user avatar
  • 6,173
0 votes
0 answers
14 views

Selection and derivation of data according to date

it is for a database joined to AppleSheet. Is there a form or function that makes the data entered according to the week go to another exclusive spreadsheet for that week? Example: Martin entered data ...
user avatar
0 votes
0 answers
15 views

YES/NO dependent drop down list

I would like to have a "YES" or "NO" dropdown list in cells A6, K6, L16 & K16. I want only one of those 4 cells to be YES and that automatically changes all other cells to NO. ...
user avatar
  • 1
0 votes
1 answer
84 views

RegEx on Google Forms: How to make Case Insensitive work?

I have been wondering, how to use case insensitive regular expression for a response validation in a form where there are different possible answers? I have tried: (name1|name2|name3) and it really ...
user avatar
  • 45
0 votes
1 answer
48 views

Google Sheets – Dynamic Calculation of Data Validation

My use case is to select between two times within a single day. The first cell ("from" time) has list of items data validation set up, where the options are as follows: 12:00 am,1:00 am,2:...
user avatar
0 votes
1 answer
25 views

Need help editing Google Scripts for multiple sheets in workbook

I am attempting to use this script to choose multiple selections in 1 cell for data validation in google sheets. I cannot get the script to work on the 200+ sheets within the workbook. Is there a way ...
user avatar
  • 1
0 votes
1 answer
29 views

Add up my week hours from selected schedules

I would like to have my day schedule to be linked to its day hours so that it produces my total week hours. For example: Schedule is selected from drop down list which is connected by data validation ...
user avatar
  • 5
0 votes
0 answers
34 views

Can I make a relational data validation with setDataValidation in google sheets?

Apologies, cannot share sheet bc company work. I have two columns. The first is data validated against a static list, the second is data validated dependent on what was selected in the first column. ...
user avatar
0 votes
1 answer
26 views

How do I conduct conditional data validation multiple times so that each input prompts a different conditional validation outcome

I am a member of a project that does electricity monitoring, and I am trying to organize our contact sheet to indicate the status of our electricity monitoring across locations. I want to make the ...
user avatar
2 votes
1 answer
541 views

Can I have a drop-down list appear in a cell only if another cell contains a specific value?

What I mean by this is that I want a cell to show a drop-down list (the only kind I know of is from Data Validation) only if another cell (NOT containing Data Validation/drop-down list) has a specific ...
user avatar
0 votes
1 answer
48 views

Conditional formatting based on string values in another column

In Google Sheet I would like to highlight in red the cells in a column "Y" if another column, say column "X", contains certain abbreviation (string variable). Column "X" ...
user avatar
0 votes
2 answers
21 views

How to extract the decimal fraction of a number for a formula?

For a column where years and months are entered as 1.02 for one year and two months, or 3.04 meaning three years and four months, how is that converted to a date? Or just expressed so that it's base ...
user avatar
0 votes
1 answer
1k views

Date comparison is not working as expected

I'm working on a small project, using Google Sheets and Google Script to generate a tennis scheduling system. Basically the user enters their info on a Google Form, which is passed to Google Sheets ...
user avatar
0 votes
1 answer
20 views

Needing to change a column to false when Data Validation is changed

I am using Google sheets and have a cell that has data validation and I am putting check boxes (I4:I) for people when items are received, they need to be changed to "False" when the Data ...
user avatar
0 votes
1 answer
327 views

Google Sheets data validation using hidden lookup value?

Let's say we have a Google Sheet used as a data validation "table" ******************************** ID | Name ******************************** 1 | Mike 2 | Sue 3 | Bob 4 ...
user avatar
  • 479
0 votes
1 answer
481 views

I need to automatically change data validation rules after adding row for all rows underneath

I am completely new to scripts. I have created a complex template on Google Sheets for accounting. I am using dependent drop-down lists. As it is a template and plan is to add rows for the items in ...
user avatar
  • 3
0 votes
1 answer
61 views

How can I use a Drop Down List and skip data validation in Google Sheets?

I want to show a Drop Down List in a cell in Google Sheets, where user can choose a list of words. But I want to let to user to input any number too. Since the only way I know to show off a drop down ...
user avatar
  • 1
1 vote
0 answers
24 views

Restrict editors to only able fill today's date in data validation date - Google Sheet

I have used the data validation date > 'valid date' so that in the range of cells I want, will already show the calendar drop down. However, would like to ask for guidance on how I can restrict the ...
user avatar
  • 11
0 votes
1 answer
119 views

Sheets - Custom Data Validation Combobox

I am having a problem with making my own custom data validation combobox, here is what I am having a problem with: Sheet (Hardware) Column A Column S: Column A = Asset Tag Column S = is Owned? Yes/No ...
user avatar
0 votes
0 answers
167 views

copy color theme from data validation cells

So I'm using data validations alot. for better organization I create tab called setup and I put my list of possible options there let's say I want to categorize my credit card expenses so I'll put a ...
user avatar
3 votes
1 answer
407 views

Why do Google Sheets filter ranges sometimes auto-expand to include new data, and sometimes don't?

When I create a filter in Google sheets, I want the range to automatically expand as I add/populate new rows. Sometimes, this works fine: When I have a column using “Checkbox” data validation (...
user avatar
-1 votes
2 answers
3k views

Sheets: Condense a column with empty cells

I have a Google Sheet with multiple columns and some of the columns have empty cells at arbitrary locations. I want to 'condense' the columns, that is, simply shift upwards all cells in the column so ...
user avatar
1 vote
1 answer
512 views

Problem with COUNTIF in Google Sheets Data Validation Custom Formula

I am following some tutorials and discussion on forcing uniqueness in Google Sheet Column (I need it for ID indexing). I have been using Data Validation for some time, but i have a problem with custom ...
user avatar
  • 11
0 votes
1 answer
33 views

Checkbox that can display a checkmark or an X

Trying to make a checkbox that with one click it displays a check and with two clicks it will display an "x". is this possible? I've been looking for a way but the only thing I can find is a drop-...
user avatar
1 vote
3 answers
174 views

Google Sheets Checkbox vs. Tickbox

Sometimes, I've seen Checkbox but I've also seen Tick Box. Does anyone know what's going on?
user avatar
  • 259
0 votes
2 answers
1k views

Dynamic Data Validation in Google Sheets

I want to restrict a value in a cell based on another cell in the same row. The value in A must not exceed what is in B. If the value in B is zero, any figure is allowed in A A | B --- 4 | 9 – ...
user avatar
0 votes
1 answer
1k views

None of the above checkbox in Google Forms

I would like to create a multiple choice checkbox question in Google Forms with a 'None of the above' option and validate that if that option is selected, none of the other options are selected. For ...
user avatar
0 votes
2 answers
71 views

Importing data found to be true from one Google sheet to another

I am trying to find a way to import data (text) from one sheet to another, via the same book. I assume it's going to need an IF or IFS formula because I only want data is that found to be "TRUE" (via ...
user avatar
  • 1
2 votes
3 answers
11k views

How do you get current date to be added to a cell when a check box is checked in another cell

I want to get the current date added into a cell when a tick box is checked in another cell. I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
user avatar
-1 votes
2 answers
8k views

Change a cell to a colour when a tick box is checked in another cell

I have a cell with a checkbox. I want another cell which contains a date to go green when the tick box is checked. I presume this is done in conditional formatting but I am struggling with it.
user avatar
1 vote
1 answer
21 views

Insert value after analysing 2 cells

I have a sheet that will a tick box in column J. When another tick box is ticked in column E I would like the sheet to check if column J has a tick in it and if so insert the value of 1.5 into column ...
user avatar
1 vote
1 answer
129 views

Inserting date automatically when a tick box is ticked

I have a simple sheet that has two columns with tick boxes in them (columns 5 and 8) The goal is when the box is ticked the date and time are inserted in the column next to it. I have got it working ...
user avatar
-1 votes
1 answer
20 views

Sorting column A with drop-down and column B with checkbox

I have a spreadsheet that I use to run a foosball tournament, and in column A I have a dropdown data validation that will let me select a players name. In Column B I have a checkbox that I check once ...
user avatar
  • 1
1 vote
2 answers
157 views

How to keep a checkbox value to reuse it later in Google Sheets

I'm working on a Google sheet actually (only for fun) and I'm actually stuck on something, as I said in the title, I want to keep a checkbox value in Google sheet. I will explain my problem. I'm ...
user avatar
  • 13
0 votes
1 answer
951 views

Data validation if no then disable cell else drop-down lists

I'm trying to do a drop-down list IF another cell doesn't equal "no" So I have two columns: one with a drop-down list containing "yes" and "no" and in the other column I want to do a drop-down list ...
user avatar
  • 1
-1 votes
1 answer
36 views

How to turn checkbox with IF formula?

I have a cell with an "end date" and I have a checkbox under an "Inactive" labelled column. I would like that checkbox to turn on when today's date is after the end date. I can't seem to add a ...
user avatar
  • 1
0 votes
1 answer
3k views

Copy data validation "list of items" from one sheet to another?

On one sheet I have a column that uses "list of items" data validation which allows for a drop-down menu to extend and a multiple of options to be selected. Is there a way to automatically populate ...
user avatar
  • 1
0 votes
1 answer
175 views

How to apply conditional data validation when a cell with a defined value is changed?

I have a spreadsheet that's basically a grid checklist, let's say 10 columns and 20 rows. There are multiple cells that have N/A as the value, and I'm worried going through the checklist that one ...
user avatar
0 votes
1 answer
1k views

Using checkbox to copy data form worksheet A to worksheet B

I am trying to use the new Checkbox function in Google Sheets. I can't figure out how If I click the checkbox in A9 on Worksheet 2 I want the Text in A10 and the $ amount in A11 to be copied to cells ...
user avatar
  • 1
1 vote
1 answer
128 views

Conditional Google Sheet Checkbox

I am creating a spreadsheet which can track all of the replacement items I am sending out for my business, however, I also want to be able to track my refunds on the same spreadsheet. I have got it so ...
user avatar
1 vote
1 answer
361 views

Conditional Format Copying in Google Sheets

I am looking to copy over a conditional format that changes the colour of an array of cells when checking a checkbox. This works perfectly fine on the first checkbox. However, when I try to replace ...
user avatar
0 votes
1 answer
35 views

Data Validation for Sheet1 from Sheet2 list

I'm trying to figure out how to validate data entries in one column on sheet 1 from a database I created in sheet 2. In column A sheet 1 the user is allowed to type in a string, upon completion it ...
user avatar
1 vote
1 answer
257 views

Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
user avatar
  • 11
0 votes
2 answers
148 views

Flag rows that are duplicates in two columns

My question is similar to this one, but adds a little more complexity. How can I flag duplicate entries in a Google Sheets column unless the duplicates have different values in another column? For ...
user avatar
  • 115
0 votes
1 answer
25 views

If checked, add row to data chart?

Basically, I have been working on some automated solutions to show the date and data for the most recent 4 weeks on this data sheet. There are still some bugs to work out, but the last thing I need to ...
user avatar
1 vote
1 answer
196 views

Restrict entry to a group of cells based on the value in another cell

I am creating a work schedule where the staff enters their own schedules. I only want a certain number of staff each shift. I already use COUNTIF to show the number of staff signed up for each shift. ...
user avatar
1 vote
1 answer
5k views

How to combine FILTER with TRANSPOSE in data validation custom formula

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text - "vs" - in them). So, I'm trying to use the ...
user avatar
  • 11
1 vote
1 answer
9 views

Max number in a column

I haven't been able to find an answer to this because I don't know how to word the search terms. How do I prevent a cell from going above the number 300,000? It is simply the sum of one column ...
user avatar
  • 13