Skip to main content

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
2 votes
1 answer
25 views

How do I set up a cell to output the mean or median of multiple datapoint entries?

In Google Sheets, I'm taking blood pressure readings and entering them into an entry sheet. It's useful to measure the pressure a few times (perhaps three) in short succession and then record the ...
Maciej Tomczak's user avatar
2 votes
1 answer
515 views

data validation case sensitive string in google sheets

I'm trying to formulate a data validation code in Google Sheets for a simple 6-letter string - any 3 capital letters and any 3 lowercase letters. I believe I should be using the RegexMatch function. ...
julia's user avatar
  • 21
-1 votes
1 answer
24 views

Create a Mailing List from Master Data with Checkbox Function in Google Sheets

I am trying to use the checkbox function of Google Sheets to filter data from one sheet to another. Basically, I would like to create a mailing list in a separate sheet. When the checkbox "Wants ...
AxelleS's user avatar
  • 11
0 votes
1 answer
46 views

I can not remove data validation with 2 criterias - Function onEdit(e)

I would like to run function onEdit(e) to remove Data validation with these 2 criterias as below: Remove for columns G,H,I,K if L is "no need". Remove for columns M,N,O,Q if R is "no ...
Caroline's user avatar
0 votes
1 answer
2k views

Google Sheets Data Validation Dropdown error: valid data is rejected as invalid

I'm trying to create a drop down list... something i've never had an issue with until GSheets modified the route in which they handle dropdown lists. Now I cannot seem to get them to work. Have tried ...
JxN's user avatar
  • 41
0 votes
1 answer
22 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 ...
Roman's user avatar
  • 103
-1 votes
1 answer
457 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 ...
Kiwipai's user avatar
0 votes
2 answers
313 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 ...
sam's user avatar
  • 6,989
2 votes
2 answers
1k 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 ...
JDD's user avatar
  • 65
0 votes
1 answer
125 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
126 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 ...
Amanda's user avatar
  • 1
0 votes
1 answer
36 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 ...
JDL's user avatar
  • 5
1 vote
1 answer
139 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 ...
Alexander's user avatar
3 votes
2 answers
8k 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 ...
Nathan96's user avatar
0 votes
2 answers
188 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 ...
Nicholas Saunders's user avatar
1 vote
1 answer
3k 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 ...
Walcuray's user avatar
0 votes
1 answer
27 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 ...
Joseph Yearous's user avatar
0 votes
1 answer
755 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 ...
Riccardo's user avatar
  • 589
0 votes
1 answer
1k 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 ...
Aure's user avatar
  • 3
3 votes
1 answer
535 views

How can I use a data validation dropdown that allows users to add new numbers?

I want to place a dropdown in a cell in Google Sheets that contains a list of words for users to choose from. I also want users to be able to ignore the words in the list and instead enter any number. ...
cafo's user avatar
  • 39
1 vote
0 answers
66 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 ...
Vidya's user avatar
  • 11
0 votes
1 answer
162 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 ...
Joshua Dela Cruz's user avatar
0 votes
0 answers
368 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 ...
Yarden Cohen's user avatar
4 votes
1 answer
1k 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 (...
Dan Duett's user avatar
0 votes
3 answers
4k 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 ...
ChaimKut's user avatar
  • 109
1 vote
1 answer
787 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 ...
osek's user avatar
  • 11
0 votes
1 answer
221 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-...
user224005's user avatar
2 votes
3 answers
260 views

Google Sheets Checkbox vs. Tickbox [closed]

Sometimes, I've seen Checkbox but I've also seen Tick Box. Does anyone know what's going on?
skube's user avatar
  • 279
1 vote
2 answers
2k 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 – ...
arul selvan's user avatar
0 votes
1 answer
2k 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 ...
MymsMan's user avatar
1 vote
2 answers
235 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 ...
Soli's user avatar
  • 11
3 votes
3 answers
14k 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, ...
Christopher Grant's user avatar
-1 votes
2 answers
9k 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.
Christopher Grant's user avatar
1 vote
1 answer
23 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 ...
ceolnamara's user avatar
1 vote
1 answer
355 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 ...
ceolnamara's user avatar
-1 votes
1 answer
23 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 ...
Bryan's user avatar
  • 1
1 vote
2 answers
312 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 ...
Sora's user avatar
  • 13
1 vote
1 answer
2k 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 ...
Joop's user avatar
  • 11
0 votes
1 answer
158 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 ...
Derek's user avatar
  • 11
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 ...
Jacob's user avatar
  • 1
-1 votes
1 answer
254 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 ...
screampuff's user avatar
1 vote
1 answer
2k 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 ...
Mike's user avatar
  • 11
1 vote
1 answer
164 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 ...
Lewis Birchall's user avatar
1 vote
1 answer
373 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 ...
Lewis Birchall's user avatar
0 votes
1 answer
42 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
0 votes
1 answer
270 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 ...
petvrg's user avatar
  • 9
1 vote
2 answers
192 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 ...
Janosh's user avatar
  • 127
0 votes
1 answer
35 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 ...
TheMrIggs's user avatar
2 votes
1 answer
229 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. ...
Frances Davis's user avatar
1 vote
1 answer
10 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 ...
Mandy R's user avatar
  • 13