Skip to main content
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.
Rubén - Volunteer Moderator -'s user avatar
3 votes

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

You can do this by using dropdown that uses a range to populate its list. Any user-entered numbers will be added to the dropdown for future users Instructions Assumes your dropdowns are in Sheet1!D2:D....
CodeCamper's user avatar
3 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 ...
Vincent's user avatar
  • 373
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.
doubleunary's user avatar
  • 14.9k
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 ...
user0's user avatar
  • 26.7k
2 votes

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 ...
Shawn's user avatar
  • 21
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:...
pnuts's user avatar
  • 17.7k
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<...
Rubén - Volunteer Moderator -'s user avatar
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(...
user0's user avatar
  • 26.7k
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 ...
Rubén - Volunteer Moderator -'s user avatar
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 ...
Daniele's user avatar
  • 2,428
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. ...
Rubén - Volunteer Moderator -'s user avatar
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....
doubleunary's user avatar
  • 14.9k
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 }, ...
doubleunary's user avatar
  • 14.9k
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 ...
Tedinoz's user avatar
  • 5,741
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 ...
Denis Howe's user avatar
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
Timea Nikolova's user avatar
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 ...
user0's user avatar
  • 26.7k
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($...
marikamitsos's user avatar
  • 12.9k
1 vote

How to turn checkbox with IF formula?

=IF(E5 < TODAY(), 0, 1) demo spreadsheet
user0's user avatar
  • 26.7k
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(...
user0's user avatar
  • 26.7k
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
user0's user avatar
  • 26.7k
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.
Rubén - Volunteer Moderator -'s user avatar
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 ...
Scott's user avatar
  • 151
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.
Rubén - Volunteer Moderator -'s user avatar
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.
Rubén - Volunteer Moderator -'s user avatar
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 / ...
user0's user avatar
  • 26.7k
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:...
user0's user avatar
  • 26.7k
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:
user0's user avatar
  • 26.7k

Only top scored, non community-wiki answers of a minimum length are eligible