New answers tagged google-sheets
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,"...
- 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/
- 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 ...
- 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 ...
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()
...
- 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
- 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.
...
- 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 ...
- 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" )
- 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 ...
- 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 ...
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
- 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....
- 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 ...
- 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 ...
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 ...
- 101
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 ...
- 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 ...
- 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 ...
- 5,789
1
vote
COUNTIFS in Google Sheets
Isn't that difficult ! :)
Try:
=COUNTIFS(A2:A,">="&B2 , A2:A,"<="&C2 )
- 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 ...
- 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)) ...
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 ...
- 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:...
- 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 ...
- 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)?)$")
- 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&...
- 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 ...
- 4,591
0
votes
IMPORTRANGE keeps broken
Try this iferror() pattern:
=iferror( importrange("...", lower("Sheet1!A2:D")), importrange("...", upper("Sheet1!A2:D")) )
- 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
- 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.
...
- 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
-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
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
-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 ...
1
vote
-1
votes
RegexReplace produces an error
In my version the error was produced by the commas (,) i had to use semicolons (;)
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)
...
- 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.
- 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 ...
- 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 ...
- 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")
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 ...
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 ...
-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
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
-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 ...
-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 ...
-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.
0
votes
Accepted
Using Google Sheets trying to remove all Chinese characters - possible?
=REGEXREPLACE(A1,"[^A-Z a-z]+","")
- 4,591
Top 50 recent answers are included