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.
60
questions
0
votes
1answer
36 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
...
0
votes
0answers
23 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 ...
0
votes
0answers
30 views
Is it possible to use a cell containing date as data validation in query statement
Is it possible to use a cell containing a date (as data validation) in a query statement?
0
votes
0answers
72 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 (...
0
votes
0answers
43 views
Google Sheets Data Validation with List Dependencies
Is this even possible with Google Sheets? I've looked around and found some explanations on how to accomplish this by using scripts. However, the issue remains because I'm unable to have my script ...
-1
votes
1answer
323 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 ...
0
votes
0answers
20 views
How to use SORT function on existing data, and not make a copy of it
I am following this tutorial to sort data using a drop down menu and a check box. My only issue is that I want to use this method on the original set of data, and not make a copy of it. I would really ...
1
vote
1answer
239 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 ...
0
votes
1answer
24 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-...
1
vote
2answers
75 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?
0
votes
2answers
552 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 – ...
0
votes
1answer
568 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 ...
0
votes
2answers
21 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 ...
2
votes
3answers
4k 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
votes
2answers
4k 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.
1
vote
1answer
19 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 ...
1
vote
1answer
81 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 ...
-1
votes
1answer
17 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 ...
1
vote
2answers
67 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 ...
0
votes
1answer
586 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 ...
-1
votes
1answer
33 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 ...
0
votes
1answer
1k 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 ...
0
votes
1answer
136 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 ...
0
votes
1answer
955 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 ...
1
vote
1answer
87 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
1answer
289 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 ...
0
votes
1answer
27 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 ...
1
vote
1answer
188 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 ...
0
votes
2answers
106 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 ...
0
votes
1answer
20 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 ...
1
vote
1answer
164 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. ...
1
vote
1answer
3k 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 ...
1
vote
1answer
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 ...
0
votes
1answer
3k 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.
...
0
votes
1answer
53 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 ...
2
votes
1answer
951 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 ...
1
vote
1answer
1k 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 ...
3
votes
1answer
13k 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 ...
0
votes
1answer
1k views
Google Sheets data validation dropdown will not show if cell contains formula
I have a data validation dropdown with a list of possible values, and if the cell is empty or has a value, the dropdown list will show correctly.
But if the cell contains a formula, the dropdown ...
0
votes
2answers
5k 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 ...
0
votes
2answers
110 views
Check button in Google Spreadsheets specific for each user
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 ...
1
vote
2answers
9k 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 ...
2
votes
1answer
875 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 ...
1
vote
1answer
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
vote
1answer
131 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 ...
3
votes
1answer
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 ...
1
vote
1answer
302 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 ...
2
votes
1answer
594 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 ...
-1
votes
1answer
657 views
In Google Sheets, I'm trying to create a drop down that reads from 2 columns but returns from 1 column [duplicate]
Basically, I have store number and Store name. I want to be able to start typing a name and select the name, but only show the store number.
If that is not possible, then I thought I can combine ...
1
vote
1answer
719 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 ...
