New answers tagged

0

Try getting rid of the ,1. That instructs SUBSTITUTE to only substitute the first instance it finds, which you don't want.


0

To Default your locale: Go to Google Drive Select (Clcik on) your Profile Pic Select "Manage your Google Account" Select Data & Personalisation Near the bottom is Language: Select your Language and then Locale, not every language is available for every Locale which is annoying, and vice versa. Also some locale are present but they do not ...


0

A small contribution: Deduplication is a little easier than shown above. Just do: =UNIQUE(AnyRange or Formula)


0

From the Google official support site DGET is the only database function that does not aggregate – it will return an error if the filtered data has either no matching elements or more than one matching element. So yes. This is the expected behaviour. As your best alternative would be to use the QUERY function =QUERY(M1:N,"select N where M='Daedalus I' ...


1

@James/mreighties posted an answer on Google, that I am copying here for future reference: Hello, I may have found the answer at this site... https://infoinspired.com/google-docs/spreadsheet/exact-match-in-database-functions-in-google-sheets/ ...and changed your formula to this... =dget(A1:B5,"Wins",{"Horse";"=Daedalus I"}) ......


0

Work around for non-numeric cells: Select the cells or column that the non-numeric data is in On the home ribbon, click the More Formats dropdown Select the "Accounting" numeric format This will insert a small amount of padding on the left side of each cell. Note: Does not work for numeric data (for obvious reasons).


-1

@jparagon 's December 2020 edit worked, but I had to set the trigger to be "onEdit", "Head", "From spreadsheet", "On form submit" (instead of "On open") in order for it to automatically wrap text upon a new submission to the form. Additionally, at the end of Step 1 in the answer above, you have to save your ...


0

You can use =IMPORTXML() which support xpath_query. In your use case, the query would be something like: =IMPORTXML("https://celsius.network/rates/", "//div[@id='ETH']") Here's the spreadsheet that I've used to make sure it's working. You can make a copy by clicking here.


0

On a German keyboard use Ctrl+ö for current date Ctrl+Shift+ö for current time Ctrl+Alt+Shift+ö for current date and time


0

This solution works: Mark the cells you want the padding (horizontally left). Go to "Custom Number Format": Enter " "@ where the whitespace represents the distance you want to have. The more whitespaces the more distance to the left. The @ sign is a placeholder for the cell's text. See also Format numbers in a spreadsheet


0

Simplest way: Show formulas -> View / Check Show Formulas Copy formulas (or cut) -> Ctrl + C | Ctrl + X Paste values -> Ctrl + Shift + V Hide Formulas -> View / Uncheck Show Formulas


1

Well hello, after much trial and error I managed to get a formula that does exactly what I want there is just one simple problem it's so long .... but maybe it can help others too or someone can come up with a way to simplify it. =SORT(IFERROR({iferror(query({sheet1!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3";0);A2:D2/0); iferror(...


0

I could Modify the code to the following: var menu = [{name: "Draw Borders", functionName: "borders"}]; SpreadsheetApp.getActiveSpreadsheet().addMenu("Borders", menu); } function borders() { var range = SpreadsheetApp.getActive().getSheetByName('Tab_Name').getDataRange(); var cell = SpreadsheetApp.getActive()....


0

I could Modify the code to the following: var menu = [{name: "Draw Borders", functionName: "borders"}]; SpreadsheetApp.getActiveSpreadsheet().addMenu("Borders", menu); } function borders() { var range = SpreadsheetApp.getActive().getSheetByName('Tab_Name').getDataRange(); var cell = SpreadsheetApp.getActive()....


0

I had this issue and found a solution. This should also update any new Google Sheets that you create. Instructions below: Open a new Google Sheet Click on File Click on Spreadsheet Settings Click on Display language, This is at the very bottom left of the interface window. Click on the Pencil next to the language Start typing English into the search bar ...


1

The closest built-in feature is Conditional Formatting unfortunately it doesn't support cell borders. One option is to use Google Apps Script. Related Automatically add borders around nonempty cells Conditionally format row borders Resources https://developers.google.com/apps-script/guides/sheets


0

Given your example — "$148 (123 euros)" — you won't simply be able to refer to the cell when trying to do math with it. Let's say that your above string is in A2 and you want to divide the USD amount by 2 in cell B2. In cell B2, you'd use something like this: =REGEXEXTRACT(A2,"\d+")/2 The REGEXEXTRACT will extract the first group of ...


0

Use the following ={QUERY({sheet1!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3"), QUERY({sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3")} (do adjust syntax according to your locale)


1

EDIT (based on your comment) No mixed characters, it will always be a single character or a repeat of a single character For a draggable formula use =SUM(INDEX(IFERROR(SEARCH(B10,$B$2:$E$7)*LEN($B$2:$E$7)))) Original answer For a single auto-expanding formula please use =INDEX(IFERROR(VLOOKUP(B10:B13, QUERY(FLATTEN(SPLIT(REGEXREPLACE(TEXTJOIN(&...


0

Given your additional information that there will be "[n]o mixed characters, it will always be a single character or a repeat of a single character," this formula should produce the result you want: =ArrayFormula(QUERY(FLATTEN(SPLIT(REGEXREPLACE(QUERY(FLATTEN(W2:Y),"SELECT * WHERE Col1 Is Not Null"),"(.)","$1~"),"~...


0

Try this formula out? =ArrayFormula(SUM(IF(ISNUMBER(FIND($W7, SPLIT(REGEXREPLACE(CONCATENATE($W$2:$Y$5),".{1}", "$0,"), ","))),1,0))) Sources: Finding occurrences of a String in a Cell REGEXREPLACE adding Delimiters My addition to the Source Material: "CONCATENATE($W$2:$Y$5)"


1

There is just a spelling mistake in your formula This part: +sum(FILTER('CC-Chase'!$F:$F,'CC-Chase'!$D:$D=("Grocery", should be: +sum(FILTER('CC-Chase'!$F:$F,'CC-Chase'!$D:$D=("Grocery"), Actually you do NOT need the parenthesis around the text values. You can replace ("Grocery") with "Grocery" EDIT (after having a ...


0

There are many ways of satisfying your question. This is just one example. use this query =query(A1:D6,"select A, B, C, D where year(A)=2021") Sample data Queried data


1

Google Apps Script services doesn't include a way to detect the if the user is using the desktop UI or the mobile app. Possible workarounds Instead of using Class UI methods like alert or prompt or Class Browser methods, set a range as a notification area and put there your messages. Instead of using Google Sheets for data entry use a Google Form or create ...


0

I modified the helper column on L5 =ArrayFormula(text(ArrayFormula(IFs(REGEXMATCH(C5:C, "-"), SUBSTITUTE(C5:C,"-",""), C5:C<>"-",C5:C*10)),"00000")) this will make all room/bed numbers in 00000 format. Then they will be sorted in the tab of (All_Patients) by this formula =query(Data_Entry!B5:L, "...


0

This is due to what's known as floating-point calculation. The short version is that numbers calculated in different ways, even if they are equal on paper, will wind up carrying small artifacts way out in the decimal range. The resolution is simple, which is to just wrap either the initial SUM formulas or the final SUM formula in ROUND(...,2), e.g. =ROUND(...


1

In Google Sheets, take a look at ISOWEEKNUM(). I created an extra column with the week number for each date, which I could then use in the pivot column under month for the same date to expand each month to week.


1

I found the answer =roundup() that will round the number to higher digit even if its less than 0.5


1

My solution does not refer to your existing sheets at all. I've added two sheets ("Master" and "Erik Help") to your sample spreadsheet, both tabs highlighted with bright green. The "Master" sheet is hidden (which I would recommend, because you won't need it in daily dealings. You can see it and Unhide it if you like by clicking ...


0

Solved It Found guidance from this article In order to test Switch back to the legacy editor Select Run > Test as add-on Configure Test Select version: Test with latest code Installation Config: Choose any of the option Select Doc: Select your sheet Save Execute Saved Test > Test


1

Try the following =TRANSPOSE({IMPORTRANGE("1JwbcOmfoRgc4s4OVN4poQ18KLWbqY2tZ22NA07ZsZIg","Sheet1!B1:H1"); {INDEX(QUERY({IMPORTRANGE("1JwbcOmfoRgc4s4OVN4poQ18KLWbqY2tZ22NA07ZsZIg","Sheet1!B1:H")}, "select "&JOIN(", ","sum(Col"&SEQUENCE(7)&")...


2

The problem is that the onOpen function is incomplete; .addToUi(); is missing. This is the onOpen function on the referred guide /** * Creates a menu entry in the Google Docs UI when the document is opened. * This method is only used by the regular add-on, and is never called by * the mobile add-on version. * * @param {object} e The event parameter for ...


0

https://infoinspired.com/google-docs/spreadsheet/find-the-last-row-in-each-group-in-google-sheets/ That answered my question. You add a helper column that will list the row number Then put this formula in next column and determine which column you look for similar values according to the explanation in the url.


0

Is this a scripting challenge for yourself? Or are you actually trying to solve a real-world problem. If the latter, you don't need script, just a formula: =REPT("A",A1)


1

Regarding point 1, there aren't enough details but even proving all the possible relevant details its very likely that only Google can answer it. Regarding point 2, you don't need to see the formatting applied to a cell to apply it to another cell, use the Paint format tool.


0

Also, if using date or datetime, it disables the proportional plotting over the x-axis.


3

You can use =INDEX(SPLIT(F22, " - "),,2) - INDEX(SPLIT(F22, " - "),,1) This means we split the same cell twice. Using INDEX we use the second column of the first split minus the first column of the second split. (Of course you should format the result as duration.)


0

If it were up to me, I would not set up your sheet the way it is. But as you have things set up, try this formula in B8: =ArrayFormula(IFERROR(VLOOKUP(B7&B9,{A2:A5&C2:C5,B2:B5;A2:A5&E2:E5,D2:D5},2,FALSE),"NO MATCH")) The formula uses curly brackets to form a virtual array with the A2:A5 values concatenated with C2:C5, and to the right ...


0

You have search values in a given column on a given sheet. You want to use those values to search for matching values in a different given column on a different sheet. If a match is found, then you want to update any differences from the search row to the matched row. If a difference is not found, you want to append the entire search row to the un-matched ...


0

You may switch the color by add "a3bc9f" (the color code in double citation mark) to change the color. Like this: =SPARKLINE(B4:C4,{"charttype","bar";"color1",if(B4>C4,"#dd7e6b","a3bc9f");"color2",if(C4>B4,"#dd7e6b","a3bc9f")})


0

I could Use this trick on the counter cells =ifs(OR(ActiveMonth="January",ActiveMonth="March",ActiveMonth="May",ActiveMonth="July",ActiveMonth="August",ActiveMonth="October",ActiveMonth="December"),Y1,OR(ActiveMonth="April",ActiveMonth="June",ActiveMonth="September&...


0

Here is a bit of hack, as previously noted, Ctrl+X for cut and then paste preserves the references (equations changed to refer to original cells from new location), it's copy and paste that updates according to the rules changing which cells are referred to). Using $s à la $A$1 version works within a sheet, but not between sheets. So... Use Ctrl+X and Ctrl+...


0

For all numbers Whether they are using thoudsand and comma separators or not (e.g. 1,000,000.00 or 1.000.000,00) IFERROR( VALUE(E1), IFERROR( VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1, ".", "~"), ",", "."), "~", ",")), VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1, ",", ...


-1

After adding the page numbers to the footer (or header), double click the footer and after the page number add "of" then go to the "Insert Tab - Page numbers - Page count. Its done.


1

Please use (Do adjust ranges to your needs) =ArrayFormula(IFERROR(VLOOKUP(G2:G, QUERY(SORTN(SORT({ROW(D2:D),D2:E},1,0),9^9,2,2,1), " select Col2, Col3 where Col2<>'' "),2,0))) How the formula works We create a virtual column based on each row, sort and query the result. We then use the VLOOKUP function ...


0

Solution to my problem and maybe the viewers problem can be fixed with this: https://stackoverflow.com/questions/33682318/copy-row-to-new-sheet-using-google-apps-script


0

Please use =QUERY(IFERROR(INDEX(REGEXEXTRACT(C2:C;"(\w+) onb"))); "where Col1<>'' ") (do adjust ranges and formula according to your locale) Functions used: QUERY INDEX IFERROR REGEXEXTRACT


3

Please use the following (Adjust ranges to your needs) =SORTN(QUERY({INDEX(ROW(D2:D)),D2:E}, "select Col2, Col3 where Col2 is not null order by Col2, Col1 desc"), 9^9,2,1,1) How the formula works We add a virtual column based on each row to our query and order the query. We then -in ...


-2

Multiply your values by -1 to switch from positive to negative and vice versa


-2

Or add <br> at the end. E.g. this is line a. <br> this is line b which becomes this is line a. this is line b.


Top 50 recent answers are included