New answers tagged

2

Google Sheets doesn't include that as a built-in feature. Please send a feature request direclty to Google through the Google Sheets Help menu It's worthy to mention that FIND AND REPLACE can highlight / navigate through the cells diplaying an error, i.e. #N/A You could even use regular expressions to search for multiple errors.


0

I found the trick to fix this issue : using the link as explained in the answer to the question mentionned in this question, in the gid=someId&range=SomeLocation part, remove the right part of the = (to end up with something like gid=someId&range=). Use the CONCATENATE function to build the HYPERLINK's first parameter and build the right part of the ...


0

Instead of using actual references, i.e. A1, use INDIRECT, i.e. INDIRECT("A1") Related Lock the formula of a cell, even if the cells it references change Prevent dragging and dropping from changing cells referencing moved cells Google Sheets Cell Reference Locking a formula when adding rows


1

Regarding =QUERY(IMPORTRANGE("SheetA","F!O:P"), “Select #(Col2) where Y N(Col1) contains ‘Y’ “) one problem is that it's using typographical quotes instead of straight quotes. As second problem is that the second argument syntax is wrong. Regarding =QUERY(IMPORTRANGE("SheetA","F!O:P"), "Select * Where Col2=<&...


2

@Riley, using SUBSTITUTE, you "wrap" like this: =ArrayFormula(SUBSTITUTE(SUBSTITUTE(C:C,"wd","Web Development"),"ch","Chores")) It's a bit like those Russian nesting dolls.


0

To determine if cell A1 contains an asterisk, use: =if(iserror(find(char(42),A1)),"No Asterisk","Asterisk")


0

You are trying to create a match condition if a name contains "*" (asterisk). However in the question that you cited, Google Sheets formula for “if contains”, none of the REGEXMATCH answers use an asterisk. In spreadsheets, the asterisk (*) is normally a wildcard character taking the place of any number of characters. But in regex, the asterisk has ...


4

Change your * to a +, so you end up with ">([^<]+)<. I don't think you want the double quotes at the end either, since Account is followed by </div>, not by something like <"div which wouldn't be valid HTML anyway. * means 0 or more matches, + means 1 or more. See e.g. Wikipedia for some information about these 'quantifiers'. Here ...


1

The problem in =IF(INDIRECT('Form Responses 3'!G$20)="","",COUNTIF('Form Responses 3'G$1:G$167,F20)) is that INDIRECT requires a text value but you are passing a reference. To fix it enclose 'Form Responses 3'!G$20 between ". Resulting formula: =IF(INDIRECT("'Form Responses 3'!G$20")="","",COUNTIF('Form ...


1

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<=B1) On the On invalid data radio buttons selet Reject input Dont' forget to click Save


2

You can try the following =ArrayFormula(REGEXREPLACE($Q26:Q,"\w+","1")) (Please adjust ranges to your needs)


0

For dynamic queues like that, Access would be better, but I think for what you are trying to do, that would be trying to use a hammer to crack a nut. It looks to me as if your problem stems from using the same rows for input and output. A possible work around would be to put the results (output cells) in a different worksheet. Then you can use filters ...


0

Although the answer provided by user0 is the best solution for this, I also wanted to add an alternative workaround for this. You can use an Array Formula to duplicate the entire "BY" column in a different column and use that duplicated column in the Query formula. Check out the following G-Sheet which shows how this workaround can be implemented (...


0

Please use the following formula ="Total: "&ArrayFormula(SUM(IFERROR(REGEXEXTRACT(B2:B,"\d{1,}")*1))) Functions used: ArrayFormula IFERROR SUM REGEXEXTRACT


1

The following formula do the job =ArrayFormula(SUM(VALUE(REGEXEXTRACT(E2:E3,"\d{1,}")))) REGEXTRACT use regular expressions to extract text and works fine together with VALUE and ARRAYFORMULA \d{1,} is a regular expression that match any digit (0-9) following by any number of digits.


0

The formula 'Sectors and subsectors'!F56 is =vlookup(B56,'Sector list'!A$2:A$40,2,FALSE) The error ocurrs b/c the third parameter is 2 but the second parameter only has one column. The formula 'Sectors and subsectors'!F57 is =lookup(B57,'Sector list'!A$2:A$40,'Sector list'!B:B) The value in B57 is Five. The error ocurrs b/c the values in 'Sector list'!A$2:...


1

@Steven, I've added a sheet ("Erik Help") with the following custom CF formula/rule applied to D4:D73 ... =NOT(ISERROR(VLOOKUP("*"&D4&"*",{C$116:C$125;E$116:E$125},1,FALSE))) NOTES: NOT(ISERROR(VLOOKUP(...))) basically means that something was found (i.e., if it's not an error when Sheets tries to find it, then it was ...


2

You can use the following formula =CONCATENATE(SORTN(ArrayFormula(IFERROR({REGEXEXTRACT($A$2:A,".*NB"), REGEXEXTRACT($A$2:A,"NB(.*)")*1})), 1,2,2,0)) Functions used: CONCATENATE SORTN ArrayFormula IFERROR REGEXEXTRACT


2

Edit. You can use Index & Match to get the reference to the max item in the array and return the matching item from C. I don't like this approach as your declaring an array twice so the computation is more expensive, I would suggest the answer by @marikamitsos =INDEX(C2:C9,MATCH(MAX( ARRAYFORMULA(INT(IFERROR(REGEXEXTRACT(C2:C9,"NB(\d+)"),...


1

Solution: Use that in cell C1: =TEXTJOIN(,,A1,B1) Output: References: TEXTJOIN


0

Say we want to sum D1, E3, and F5, but only if they are numbers. If we use: =sum(D1,E3,F5) any of the cells that happens to be or become Text will be treated as zero: The SUM() function already tests the cells to be sure they are numeric.


1

How to Add Text to Beginning or End of Cells without a formula Please follow these steps Step 1 Go to the top menu --> Edit --> Find and replace Step 2 From Search choose Specific range and clicking on the little icon pick your range. Step 3 Check the Search using regular expressions and un-check Match case Step 4 To add text in the end, on the ...


1

You mentioned ...select the dishes in a single cell in this manner: ...just dump them into the column A, dishes search section in Search for Dish tab ...query should grab the relevant dishes from the Inventory tab, omitting the numbers before the dish name Place the following in cell B3 in your NEW Search for Dish tab. =IFERROR(TRANSPOSE( ...


Top 50 recent answers are included