5
votes
Accepted
Can I hide the dropdown arrows from Data Validation in Google Sheets?
Yes you can, just un-check the "Show dropdown list in cell" checkbox on the data validation dialog.
- 44.6k
3
votes
Accepted
Is there a way to dynamically populate a dropdown depending on the value of other cells?
You could make the second validation list an if/then statement dependent on the answer to the first one.
=if(A1="Movies","Horror","Baseball")
=if(A1="Movies","Romance","Basketball")
An example is ...
- 1,844
3
votes
RegEx on Google Forms: How to make Case Insensitive work?
Use a non-capturing group to set flags, like this:
(?i)(name1|name2|name3)
See RE2.
- 13.2k
2
votes
Is it possible to create a Named Range that is the union of two other Named Ranges?
while answer would be yes for cases when you use named ranges in formulas, this is not possible in data validation, because data validation doesn't support custom formulas for input and also doesn't ...
- 26.2k
2
votes
Grab data values from Row with max value
You have not mentioned what you would like to so with the three values mentioned, but maybe try:
=index(B:BB,match(max(A1:A10),A:A,0))
or perhaps:
=index(B:B,match(max(A1:A10),A:A,0))&index(C:...
- 17.4k
2
votes
Dynamic Data Validation in Google Sheets
Set the data validation for A1 as follow:
On Cell range set the cell range from Column A.
On the Criteria dropdown select: Custom formula is
On the formula input box add this formula: =OR(B1=0,A1<...
- 44.6k
2
votes
How do you get current date to be added to a cell when a check box is checked in another cell
I adapted user0's answer to have the following additional features:
It works in a multi-worksheet spreadsheet by specifying which sheet the change will occur in, as opposed to making the same change ...
- 353
2
votes
Dropdown list based on the result of another cell's dropdown
here is a simple formula solution:
Sheet1:
D20: Data Validation > List of items: I made a choice,I didn't make a choice
F20: Data Validation > List from a range: Sheet2!D8:D11
Sheet2:
D8: =IF(...
- 26.2k
2
votes
Can I have a drop-down list appear in a cell only if another cell contains a specific value?
Data validation dropdowns only are shown when the valid values is set as list of values or range. The only way to automatically add/remove the data-validation dropdown button is by using a Google Apps ...
- 44.6k
2
votes
How do I keep exact text formatting when using a data validation dropdown list in Google Sheets?
It would not be possible to have a drop-down list with a mixture of plain and bold text ... but using a particular encoding it will be possible. The formula is quite complex, take it apart function by ...
- 2,338
1
vote
Date comparison is not working as expected
It looks that you are new to Google Apps Script and to JavaScript.
First you should be aware that Google Apps Script / JavaScript do not handle dates the same way that Google Sheets / spreadsheets do. ...
- 44.6k
1
vote
Accepted
Needing to change a column to false when Data Validation is changed
You can fix the code like this:
function onEdit(e) {
const sheet = e.range.getSheet();
if (e.range.getA1Notation() !== 'C2'
|| sheet.getName() !== 'Outbound Parts') {
return;
}
sheet....
- 13.2k
1
vote
Accepted
Google Sheets data validation using hidden lookup value?
The drop-down list cell will always show the value you selected, but you can look up the ID in a nearby cell with something like this:
=iferror( vlookup(D2, { DropdownLists!B2:B, DropdownLists!A2:A }, ...
- 13.2k
1
vote
Accepted
Sheets - Custom Data Validation Combobox
You can't create a data validation dropdown using a custom formula such as query or filter.
However, there is a workaround:
create the relevant query/filter on another sheet,
refer to the data range ...
- 5,520
1
vote
Why do Google Sheets filter ranges sometimes auto-expand to include new data, and sometimes don't?
This bothered me too. The trick is to not use "Add N more rows at the bottom" but rather, ensure that your ranges include an empty row at the end and always insert rows above it. Rows ...
- 111
1
vote
Problem with COUNTIF in Google Sheets Data Validation Custom Formula
Try to input this formula instead of yours. Maybe is just your locale settings that prevents you from using the formula. Try semicolon instead of comma:
=COUNTIF($A:$A; A1)<2
1
vote
Checkbox that can display a checkmark or an X
That is unfortunately not possible. Best you can have is a fake custom checkbox, meaning - it will look like exactly how you wish but it won't be clickable (but it could be managed by formula or ...
- 26.2k
1
vote
Change a cell to a colour when a tick box is checked in another cell
I have a cell with a check box. I want another cell which contains a
date to go green when the tick box is checked.
You go to Format, Conditional Formatting and use the Custom formula:
=COUNTIF($...
- 12.6k
1
vote
Flag rows that are duplicates in two columns
this custom formula is able to flag and protect all duplicate pairs:
=IF(LEN(A1:A), NOT(COUNTIF(
ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE($A$1:$B), , ROWS($A$1:$A))))),
ARRAYFORMULA(TRIM(...
- 26.2k
1
vote
Accepted
Flag rows that are duplicates in two columns
this custom formula is able to flag 1st (cell) occurrence of a duplicate pair:
=COUNTIFS($A1:$A, "="&A1,
$B1:$B, "="&B1)<2
- 26.2k
1
vote
In Google Sheets, I'm trying to create a drop down that reads from 2 columns but returns from 1 column
Data validation drop drowns in Google Sheets can't pull data from two columns.
One solution is the one that you already figured out, join the values of those columns and the use those joined values.
- 44.6k
1
vote
Accepted
Data Validation rule to enforce first day of a month
Custom formula type of data validation is evaluated independently of the cell. In other words, I was assuming that it was checking if the value returned by the expression was equal to the current ...
- 141
1
vote
Validate with "List from a range" only if another cell is not empty on Google Sheets
List from a range option of data validation doesn't work that way. You could try to use a custom formula, but this option doesn't show a dropdown.
- 44.6k
1
vote
Google Sheets - How to format numerical contents of data validation list?
AFAIK, at this time it's not possible to apply the desired format (0.00), but other formats like date, percentage works fine. Currency also doesn't work.
- 44.6k
1
vote
How do you link the value of a checkbox to another checkbox
The image answer got me thinking and this seems to work best.
Insert a checkbox in each cell you want them located
Use your arrow key to move to the box you want to be linked
You will notice the ...
- 11
1
vote
Accepted
How do you link the value of a checkbox to another checkbox
This can't be done via "standard" means. Formulas are not permitted as custom checked/unchecked values.
here is one of many methods of how to fake it:
decide on cell height / width / color / ...
- 26.2k
1
vote
Data Validation with an IF formula
yes that is possible. check this example:
Sheet1:
D20: Data Validation > List of items: I made a choice,I didn't make a choice
F20: Data Validation > List from a range: Sheet2!D8:D11
Sheet2:
D8:...
- 26.2k
1
vote
Accepted
Google Sheets data validation dropdown will not show if cell contains formula
pretty much yes, to return on selectable items on dropdown menu you need to select a cell and delete it with Delete
to avoid it use green aproach:
- 26.2k
Only top scored, non community-wiki answers of a minimum length are eligible