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
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
1
answer
30
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
1
answer
240
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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. ...
CommunityBot
- 1
1
vote
2
answers
377
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 ...
CommunityBot
- 1
0
votes
1
answer
100
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
-1
votes
1
answer
284
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 ...
- 44.6k
0
votes
1
answer
34
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 ...
CommunityBot
- 1
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. ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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?
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
1
answer
267
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 ...
CommunityBot
- 1
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 ...
- 11
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
votes
1
answer
17
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 ...
- 13.2k
0
votes
1
answer
660
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 ...
- 13.2k
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 ...
- 44.6k
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 ...
- 1
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 (...
- 111
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 ...
- 13.2k
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 ...
- 9,916
0
votes
1
answer
102
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:...
- 2,338
0
votes
1
answer
35
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 ...
- 5,520
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 ...
- 9,916
0
votes
1
answer
99
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-...
CommunityBot
- 1
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 ...
- 4,980
0
votes
1
answer
23
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 ...
- 13.2k
0
votes
1
answer
630
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 ...
- 44.6k
0
votes
1
answer
992
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 ...
- 13.2k
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 ...
- 9,916
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, ...
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 – ...
- 44.6k
0
votes
1
answer
158
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
...
- 5,520
0
votes
0
answers
321
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 ...
- 101
-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.
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 ...
CommunityBot
- 1
1
vote
1
answer
699
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 ...
- 9,916
0
votes
2
answers
8k
views
Disable a cell when A cell is blank
I want to create a google spreadsheet where I have 2 columns involved.
In my A column, I have data and column B has a drop-down list. What I want to do is disable column B unless column A has a ...
- 12.6k
-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 ...
CommunityBot
- 1
-1
votes
1
answer
87
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 ...
CommunityBot
- 1