New answers tagged

1 vote

Wikipedia List Scrapping of Multiple Lists

The ImportHTML function can have a maximum of 4 parameters, which is why your first formula seemed to work. Try doing something like this: ={ IMPORTHTML(J1,"list" ,2); IMPORTHTML(J1,"...
user avatar
  • 506
0 votes

Can I have a drop-down list appear in a cell only if another cell contains a specific value?

Sounds like you are looking to create a dependent drop down list. If you are, this video will help you create what you are looking for: https://trumpexcel.com/dependent-drop-down-list-in-excel/
user avatar
  • 1
0 votes

Getting a count once certain criteria are met

Try this formula in Cell G2: =ARRAYFORMULA({"Full Boxes"; IF(len(A3:A),if(iferror(vlookup(B3:B,query({query({A2:F},"select Col2, count(Col2) where Col1 is not null group by Col2 label ...
user avatar
  • 4,591
0 votes

Google Sheet: is it possible to make a chart's vertical axis' Min and Max values automatically adjust according to the values in the dataset?

I had a similar situation. This Charting Example sheet has sample data to show how it works. The basic idea is to add two points to the data that's being graphed — the minimum and maximum values in ...
user avatar
3 votes

Running onEdit script only in certain sheet

Add a sheet name test like this: function onEdit(e) { const sheet = e.range.getSheet(); if (sheet.getName() !== 'Invoice') { return; } const criteria = SpreadsheetApp.newFilterCriteria() ...
user avatar
  • 5,789
0 votes

Google Spreadsheets Charts: Drawing continuous chart based on multiple data ranges

The solution to this problem is to create data in two columns for the x-axis as outlined in How to merge x-axis data from two ranges? Data and chart Basic Chart details
user avatar
  • 4,591
1 vote
Accepted

How to conditional format in Google Sheets based on a external resource

Instead of referencing C2, you need to reference $C2. There are two Conditional formatting rules. Each contains a unique custom formula - one each for the value greater/less than than your buy price. ...
user avatar
  • 4,591
-1 votes

Trying to auto-populate cells based off a dropdown selection

It is easy to make the rows gray based on the Y/N value of the cells in column A ... but it is impossible to automatically fill them with N/A based on a formula, because then you would not be able to ...
user avatar
  • 506
0 votes

Formula that Does NOT Contain 2 Different Texts

In Conditional Formatting try with a custom formula like this: =and (B8:B <> "JFK", B8:B <> "NYS" , B8:B <> "JFK/NYS" )
user avatar
  • 506
-1 votes

Getting a count once certain criteria are met

I think that this formula could solve your problem: =index(If(COUNTIFS(B2:B,"="&A2:A,F2:F,"=YES")>=20,1,)) or edit the test file that I am attaching to you, writing ...
user avatar
  • 506
0 votes

Force the refresh of a Google Sheet

I've decided to give up on this one and go back to using Excel. I've tried as many experiments as I can without any luck. If I choose to stick with Sheets, I'll just do my manual workaround of ...
user avatar
1 vote

Formula that Does NOT Contain 2 Different Texts

Have you tried checking the options under Conditional Formatting You can add multiple rules to a single cell and apply the rules to other cells afterwards
user avatar
  • 136
0 votes

Google Sheet Regex URL Replace

Use this formula (Where A2 is your url without the https) =IF(REGEXMATCH(A1,"^https?:\/\/[\w\.]*"),A1,"https://" & A1) Edit function onEdit(e) { const s = SpreadsheetApp....
user avatar
  • 303
0 votes

Google sheets/Excel: Rolling average for the past three months on different sheets

Choose Insert > Sheet, name the new sheet Helper, and put this { array expression } in cell A1 of the new sheet: ={ indirect('My report sheet'!AD3 & "!A3:K"); indirect('My ...
user avatar
  • 5,789
-1 votes

COUNTIFS in Google Sheets

below the formula gives a calculation of 22 but should actually be 16. i am definitely still doing something wrong. it first gave me an error "Iterative calculation" so i switched on under ...
user avatar
0 votes

File import into sheets is dropping necessary leading spaces

I had this same issue - I used multiple cursors in vsCode to add characters to the start of each line, saved it as a text file, then imported that to a Google Sheet. Then I could search/replace those ...
user avatar
1 vote

Set cell to reject wrongly formatted data

Same of David but validate as time with custom formula: =TIMEVALUE(A2:A)>0 (assuming that column A contains the times) I think that you could investigate even on a validation based on regex, see ...
user avatar
  • 506
1 vote
Accepted

Set cell to reject wrongly formatted data

Data > Data Validation Then select for the Criteria "Date" and "is valid date". Set On invalid data to reject input. Now I now that this does have the limitation that they could ...
user avatar
  • 303
0 votes

Force the refresh of a Google Sheet

You can get importdata() to refresh by modifying its parameter, i.e., the URL endpoint. This also lets you duck Google's cache and fetch new results. With many endpoints, you can append a no-operation ...
user avatar
  • 5,789
1 vote

COUNTIFS in Google Sheets

Isn't that difficult ! :) Try: =COUNTIFS(A2:A,">="&B2 , A2:A,"<="&C2 )
user avatar
  • 506
0 votes

Force the refresh of a Google Sheet

For more reliable scripts, limit the simple onOpen trigger to add the custom menu. If you need to do some something else when the spreadsheet is opened, use a installable trigger instead. This ...
user avatar
  • 41.6k
0 votes
Accepted

What formula can I use in order to calculate Z-Score within a specific rolling range?

The first thing to do is to make a column containing the relevant year for each team, which is stored in the first four characters of your "Team" column. You could use =VALUE(LEFT(A2, 4)) ...
user avatar
0 votes

How can I select a subset of random elements from a list in Google Sheets?

I recently came across a similar issue to this. Instead of exactly 2 items, I wanted to select a random number of items from a list. This is how I did it, selecting a random number of values from the ...
user avatar
  • 101
0 votes

I want to see the details of how a sumifs value was created

Choose Insert > Sheet, and copy your sumifs() formula into cell A1 there. Then edit the formula to replace sumifs() with filter(), like this: =sumifs(Data!G2:G, Data!F2:F, "deal", Data!G2:...
user avatar
  • 5,789
0 votes
Accepted

Replacing two distinct values inside a formulas at once using Control + H in Google Sheets

There is a logical problem with what you are suggesting. If you were to actually try your SUBSTITUTE formula, you would see that everything would wind up GREEN in the end, because you would have ...
user avatar
  • 4,402
1 vote

Extract a code from the end of a text string that contains Chinese characters in Google Sheets

As I already answered in your previous question (now deleted), use regextract(), like this: =regexextract(A2, "([-\w\.]+?)(\d\d(?:\.\d)?)$")
user avatar
  • 5,789
0 votes

Importrange with multiple criteria

QUERY The query needs to reference the IMPORTRANGE , though in this case, the entire range can be covered in one statement. =query({{"Date","Email","Name","Partner&...
user avatar
  • 4,591
0 votes

Importrange with multiple criteria

The range for date is not the same as the range for the "partner" designation. So, you need to identify separate criteria ranges for each element of your parameters Say you data looks like ...
user avatar
  • 4,591
0 votes

IMPORTRANGE keeps broken

Try this iferror() pattern: =iferror( importrange("...", lower("Sheet1!A2:D")), importrange("...", upper("Sheet1!A2:D")) )
user avatar
  • 5,789
1 vote

Google spreadsheet : impose formatting within constructed string in a cell

It is not very easy but it can be done without a script, taking advantage of the very high unicode code like 119808 for a bold A. See this test sheet
user avatar
  • 506
1 vote

Google spreadsheet : impose formatting within constructed string in a cell

That cannot be done with a custom function, because custom functions cannot format cells. They can only return a value or an array of values that is used when evaluating the formula they are part of. ...
user avatar
  • 5,789
-2 votes

Importrange with multiple criteria

Try using query function (use "Select * where ", and you can add multiple criteria with one query) thank you
user avatar
-1 votes

How to create a filter as a button on the Google Spreadsheet?

these are slicers. they have used slicer to create these. go to "Data" tab from menu .. "add Slicer" thank you
user avatar
0 votes

Sum all values of drinking for a certain day

what you could do is 1. Add the date in all cells in Column-a (Ex. 5Mai 2022 from A50 to A55, 6 MAi 2022 form A51 to A63, and so on) Use sumif function This will solve the issue
user avatar
-1 votes

How do I automatically Split data as new rows are added to the sheet?

Is happens if you are using forms, and the response sheet for the same. what you can do is, add a column before the timestamp column in the response sheet use query function to pick the entire data ...
user avatar
1 vote

Sum all values of drinking for a certain day

I ended up using a SUMIF: =SUMIF(A:A; "="&G5; C:C)
user avatar
-1 votes

RegexReplace produces an error

In my version the error was produced by the commas (,) i had to use semicolons (;)
user avatar
2 votes
Accepted

Sum all values of drinking for a certain day

If you can repeat the date on every row, use query() to aggregate the quantities by day. Put this formula in cell E1 or another free range outside of A1:D: =query( A1:D, "select A, sum(C) ...
user avatar
  • 5,789
1 vote

How to create a filter as a button on the Google Spreadsheet?

Look into slicers. Select your range, then go to Data > Add a Slicer.
user avatar
  • 303
0 votes

Simplify formula that checks student year level and mark to a letter grade

link to my sheet. I basically used VLOOKUP on its true state. According to the Documentation: If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If ...
user avatar
  • 303
0 votes

How to import a JSON-style table [['a', 'b'], ['c', 'd']] into Google Sheets?

Create a new script: /** * Evaluates JS code. * * @param {code} code The code to evaluate. * @return The result from evaluating the code. * @customfunction */ function RUNJS(code) { return ...
user avatar
  • 506
0 votes

Formula in Google Sheets should return a text string representing a date, but returns integer instead

You can use the text function: =TEXT(INDEX(SPLIT('Raw Data'!A2:A,"_"),,6),"DD-MMM-YYYY") Or use: =TEXT(INDEX(SPLIT('Raw Data'!A2:A,"_"),,6),"DDMMMYYYY")
user avatar
0 votes

How do I automatically record finish position?

Instead what you can do is, you can enter the time at what time this task was completed After the entries are made, you can you the "=rank()" function to rank based on the time field Hope ...
user avatar
0 votes

How to clean up the contact information in Google Sheet

You can use a split function =Split("cellnumber","Split by "space", or ",") =Split(A2,",") Let me know if you need help with this You can search for split ...
user avatar
-1 votes

Linking the same cell from multiple sheets (in same doc) to a summary page without having to do it one by one

you can try query function to pull the data to one sheet You can see the below link video for help. https://www.youtube.com/watch?v=_N5zhAipVn0 Thank you Regards Supreeth
user avatar
0 votes

Problems with COUNT.IF function in Google Spreadsheets

you can try the below formula =countif($K:$K,"YES") This will count cells with yes Let me know if you need help Thank you regards Supreeth
user avatar
-1 votes

Google Sheets Formula returning zero when it shouldn't

you can try one of the below formula 1.This will return 0 if error =iferror(value(mid($P$8,search(".",$P$8)-3,1)),"0") 2.This will give you a "blank call" if there is an ...
user avatar
-1 votes

How can I pull Drive hyperlinks into a Google Sheet?

What I would suggest is , you could just create a google form with the file upload option. You will be able to get the links for the image files uploaded into google sheet automatically. Let me know ...
user avatar
-2 votes

Query(IMPORTRANGE) function to skip columns

I think you will be able to ship columns with one query function, if you can share your sample dummy data, I could he you will the query function for the same.
user avatar
0 votes
Accepted

Using Google Sheets trying to remove all Chinese characters - possible?

=REGEXREPLACE(A1,"[^A-Z a-z]+","")
user avatar
  • 4,591

Top 50 recent answers are included