4

Not possible, there is no Data validation preview in Google Sheets where you would see the list of all rules you created (like in Conditional formatting, Protected ranges or Named ranges).


2

when referencing a Named range in Data validation, you don't need to include sheet name:


2

Your formula is correct. You just need to use single ' instead of double " quotes. =INDEX('Value Reference'!B2:AJ6,MATCH(B4,'Value Reference'!A2:A6,0),MATCH(B5,'Value Reference'!B1:AJ1,0)) EDIT Make Data validation dynamic You can improve your results even further. Instead of using List of items for your Data validation, you can use List from a range. ...


1

Step1 Create a new sheet called AllDays in cell B1 put =Arrayformula(if(A:A,,"'Day"&row(A:A)&"'!P39")) Step2 In cell C1 put =iferror(indirect(B:B),"") Step3 Manually drag cell C1 to the bottom of the spreadsheet Now you can simply sum this AllDays column C to get the total. Should be pretty simple to use the other columns to grab other specific ...


1

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

You are trying to associate a "text value " in a data validation cell with a given numerical value. One supposes that this is so that you can use the numerical value in another formula. It is common practice in forms for each select item to have a description AND a separate "value", unfortunately Google Sheets does not offer this. The cell that you use for ...


1

Try this in 'Main Sheet'!B6: =query(transpose(query('Value Reference'!A1:AJ7,"Select * where A = '"&'Main Sheet'!B4&"'",-1)),"SELECT Col2 where Col1 = '"&'Main Sheet'!B5&"'",0) 2nd Solution : =QUERY(TRANSPOSE('Value Reference'!B1:AJ7), "SELECT Col"&filter(ArrayFormula(IF('Main sheet'!B4='Value Reference'!A2:A7,ROW('Value ...


1

Instead of doing it individually for each cell, you are able to apply it to the whole range at once by adding :C at the end like: To override multiple cells in the range you are able to reference the start point and then manage the whole area just from one cell. For example, if you paste =C$5 into C6 cell and drag down to reach C15 then you are able to ...


1

Google Sheets data validation only helps to prevent wrong in-cell data entering. You could use a custom formula to handle conditions but any editor could remove the data validation it could not directly prevent other actions, like disallow the "form submission". As you already using buttons we could assume that you are using Google Apps Script. You could ...


1

to color it based on Transfer in B column: =REGEXMATCH(B4,"Transfer")


1

Use regular expressions and REGEXMATCH I think that =REGEXMATCH(A1,"^\[.*]$") should work.


1

EXAMPLE 1: 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(Sheet1!D20 = B6; QUERY(B8:B11;;-1);IF(Sheet1!D20 = C6; QUERY(C8:C11;;-1);)) EXAMPLE 2: formula in D2: =IF(A2=F2, {H3:H}, IF(A2=F3, {I3:I}, IF(A2=F4, {J3:J}, ))) data validation in ...


1

No, there is no workaround for getting a named range with space. Range names: Can contain only letters, numbers, and underscores. Can't start with a number, or the words "true" or "false." Can't contain any spaces or punctuation. Must be 1–250 characters. Can't be in either A1 or R1C1 syntax. For example, you might get an error if you give ...


1

As I can see you need a report about all cells with data validation rules. The next code creates the sheet with name 'dataValidation report' into the active spreadsheet and puts a little report to there. function genDataValidationReport() { var res = Sheets.Spreadsheets.get(SpreadsheetApp.getActive().getId(), { fields: 'sheets(properties(title),data(...


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