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.
76
questions
6
votes
2
answers
4k
views
Google Forms Answer Validation Based On Another Previous Given Answer
I am working on a form in Google Forms.
At some point during my form, I need to validate an answer based in a previous answer.
To make it more clear, I give you an example:
How many hours do you ...
- 61
4
votes
2
answers
3k
views
Dropdown list based on the result of another cell's dropdown
I want to populate a dropdown list based on a selection of another cell's dropdown list.
Here is my spreadsheet.
E.g., if the user does not make a choice from D20 dropdown menu, therefore F20 ...
- 71
3
votes
1
answer
760
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 (...
- 31
3
votes
1
answer
18k
views
Data Validation with an IF formula
I've got a list of items that I want to include in a Drop Down. Using data validation, all I need to do is add the range and this works. However, the list has various types of data and I want to ...
Jordan Tuck
3
votes
1
answer
4k
views
Copy data validation rules with relative references using a script
I'm looking for a way to copy my data validation the following way.
If validation for Main sheet A1 is:
Cell Range: MainSheet!J1
List from range: ListValues!1:1
I need validation for main sheet A2 ...
3
votes
1
answer
8k
views
How to set Custom data validation in Google Sheets (A really simple step-by-step guide needed)
I'm trying to figure out how to create custom data validation in Google Sheets what are the steps I need to follow to achieve this?
For example if I wanted the following validation:
// Set the data ...
- 331
2
votes
2
answers
4k
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 ...
- 21
2
votes
1
answer
974
views
Is there a way to dynamically populate a dropdown depending on the value of other cells?
Is there a way to populate a dropdown list differently depending on the values of another cell? For example, the dropdown in A2 contains Movies and Sports. I want the dropdown in A3 to have Horror and ...
- 381
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 ...
- 65
2
votes
3
answers
13k
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, ...
2
votes
1
answer
2k
views
Is it possible to create a Named Range that is the union of two other Named Ranges?
Say I have two named ranges,
team_a_coaches
team_a_players
Is it possible to have another named range,
team_a
and define it as the union of the first two named ranges?
The goal would be that if I ...
- 121
2
votes
1
answer
1k
views
Google-speadsheet data validation to restrict to Mondays only
I have a field in Google Sheets I labeled "Week of". I want to restrict user input to a date (mm/dd/yyyy) which is a Monday. I can set "Data validation" -> "criteria" to "Date" but how do I limit it ...
- 21
2
votes
1
answer
268
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. ...
- 21
2
votes
1
answer
775
views
Duplicate sheet references original sheet's range for validation
When you set up an in-cell drop down list using Data Validation that references an existing range of cells within your spreadsheet as the criteria, by clicking Data > Validation and choosing List from ...
- 121
2
votes
1
answer
736
views
Google Sheets - How to format numerical contents of data validation list?
I'm using Data Validation to give the user a list of valid prices they can select from. These are on their sheet in "financial" format such as
2.00
2.50
3.00
etc. But in the actual dropdown menu, ...
- 1,192
2
votes
1
answer
1k
views
Limit values to other ranges
I'm trying to create a Sheets that defines various objects, and these objects sometimes reference each other (equivalent to SQL foreign key).
Is there a way that I can limit the values of a cell, to ...
- 173
2
votes
3
answers
248
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?
- 279
2
votes
1
answer
209
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. ...
2
votes
1
answer
2k
views
Any way to separately Validate and create a Dropdowns in Google Sheets?
Sample Scenario:
Sheet 1 has a 3 Dropdowns from sheet 2
Sheet 2 has two lists, {"A";"B"}, and a second dynamic list that copies the first IF the value doesn't already appear on sheet 1
The behaviour ...
- 21
1
vote
2
answers
4k
views
How do you link the value of a checkbox to another checkbox
I am using the checkbox feature in Google Sheets and I have another checkbox that I would like to be ticked when the original one is ticked.
I tried using the formula where the original checkbox is ...
- 13
1
vote
1
answer
1k
views
Grab data values from Row with max value
First step I do is find the column row with the max value through =MAX(A1:a10).
Let's say it's A7 that has the max value.
Once I have that value, I would like some data from the rest of row 7 to be ...
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 ...
- 11
1
vote
1
answer
697
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 ...
- 11
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 – ...
- 163
1
vote
1
answer
165
views
Google Sheets Dropdown using Multiple Sets of Data
On a Google Sheet, I want to have a dropdown list of options to choose from. However, these options are split into different sections and so I would need to select multiple sets of data. I can get the ...
- 23
1
vote
1
answer
1k
views
Data Validation rule to enforce first day of a month
I have a Google Sheet in which users key in records for a specific month. A report on another tab looks up the month and consolidates it. I'm trying to enforce using the first day of the month to ...
- 141
1
vote
1
answer
740
views
Validate with "List from a range" only if another cell is not empty on Google Sheets
I would like to use validation "List from a range" in all the cells of column B only if in the correspondent cell in column A there is data in it.
Is there a way to do it?
- 53
1
vote
1
answer
121
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 ...
- 11
1
vote
1
answer
365
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 ...
- 11
1
vote
0
answers
55
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 ...
- 11
1
vote
2
answers
174
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 ...
- 11
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 ...
- 25
1
vote
1
answer
259
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 ...
- 25
1
vote
2
answers
240
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 ...
- 13
1
vote
1
answer
1k
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 ...
- 11
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 ...
- 11
1
vote
1
answer
161
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 ...
1
vote
1
answer
369
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 ...
1
vote
1
answer
6k
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 ...
- 11
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 ...
- 13
1
vote
1
answer
72
views
If I set the school to "School A" then only School A's classes should be visible
I have created a progress tracker for the Academy Trust I work in. The trust at the moment consist of 4 schools. Students move between the schools as the progress and between classes during the year ...
1
vote
2
answers
13k
views
How to validate a date in Google Forms?
Google Forms include the possibility to perform some sort of validation on user input for checkboxes and text; the functionality is available very easily both via the UI and the API. Unfortunately, it ...
- 123
1
vote
2
answers
376
views
Check button in Google Spreadsheets specific for each user [closed]
I have a Google Spreadsheet with different tasks. I want to implement a check button in the following way:
My intention is that the users can't edit the spreadsheet but they have to be able to use ...
- 111
1
vote
2
answers
1k
views
Google sheet data validation, text only
I want to make a column (lets say column A) that you can only enter text into it, and reject all numbers.
I have tried "custom" and "=istext()" for the formula... other options don't seem to work
...
- 1,014
0
votes
1
answer
9k
views
Can I hide the dropdown arrows from Data Validation in Google Sheets?
I use Data Validation with List from a range set up on all my rows in a column to be able to choose a "Category"
The arrows however, are annoying to see - especially on cells I haven't yet filled.
...
- 218
0
votes
2
answers
226
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 ...
- 6,823
0
votes
2
answers
120
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 ...
0
votes
2
answers
186
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 ...
- 117
0
votes
1
answer
657
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 ...
- 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 ...
- 103