Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.
7,552
questions
0
votes
1answer
19 views
Problems merging 4 columns into 1 and charting them in Data Studio
I would need to unify the data from columns Metod_1,Metod_2,Metod_3,Metod_4 and Metod_5 into a single column, but keeping the resolution date data associated to each row:
Having resolved the above ...
0
votes
1answer
6 views
Creating a formula / macro to compare two sets of data and if they match to change the colour of a cell
I am trying to work out a formula or macro to automate a task that is laborious.
I have to compare two sets of data and if they match I change the colour of a cell to show the task has been completed ...
-1
votes
0answers
20 views
How to check if every cell in a specified range contains a value and if its true go two rows to the right and add the value to a sum?
I have a lifting spreadsheet. In the spreadsheet, I track sets, and I also have a keycode system to track the muscle group that was used in each exercise. For each exercise I put down the keycode for ...
0
votes
1answer
22 views
Auto-Populate document name into cell in Sheets
I'm looking to auto-populate the document's name into a single cell in sheets no matter if/when the doc name is changed. I can enter a formula from a script =DocName(), but it won't update when the ...
0
votes
1answer
28 views
In Google Sheets, can you repeatedly autofill until next non-blank cell or until a value in another column changes, whichever comes sooner?
I'm trying to format a CSV file that contains a huge list of products (8000+) that are on separate rows but need all the fields to be on the same row. The trouble is that some of the cells are ...
1
vote
1answer
12 views
Google Sheets Conditional Formatting rows based on color adjacent
I am using Google Sheets to make a homework spreadsheet and have used conditional formatting to arrange assignments by the due date and also each class has a color. When I type in a class it ...
0
votes
1answer
19 views
AVERAGEIF get a column by a month name in its header
Basically, I have a table with a bunch of people names vertically and months listed horizontally. Then I have a drop-down list of all months. For the purposes of =AVERAGEIF I want to use that drop-...
-1
votes
1answer
12 views
How to use a function as a row number in Google Sheets [duplicate]
Let's say I want to print the value contained in D7 (Row 7, Column D) into another cell.
I would use =D7.
Great.
But what if I have a function that returns a number, like MATCH, and I want to use that ...
0
votes
0answers
17 views
LET function in Google Sheets? [closed]
All my research so far indicates that the LET function, introduced into Excel a year ago or so, is not available for Google Sheets as yet. However, I found this page:
https://tech.paayi.com/learn-how-...
0
votes
1answer
28 views
Sorting columns within a row, alphabetically
I have let's say I have rows in the following order:
SN
name1
grade1
name2
grade2
name3
grade3
1
Alice
97
Bob
90
Carl
80
2
Bob
80
Alice
100
Carl
95
3
Alice
100
Carl
80
Bob
90
Is there a way to ...
-2
votes
0answers
21 views
Have to get access to my database
Here is the I got: Error linking spreadsheets. Make sure you have access to the sheet you're trying to import from.
Have can I have access to my DAtaBase spreadsheet. I am importing my contacts to my ...
0
votes
1answer
21 views
Trying to get array formula to work on Sheets
I am trying to to use the Array formula that includes a minus and divide value. Here is my formula:-
IF(J3:J="",,SUM(((B3:B+C3:C)+(E3:E+F3:F))-$X$3)/$X$3)
I tried inserting MINUS( before the ...
0
votes
0answers
21 views
Using Offset for a dynamic range, Would like to include another duplicate range within formula
So I was given a formula which searches for values on columns B (buy/sell), C(ticker) and D(no of shares) with a reference cell A1 which indicates 'table' height.
=UNIQUE(FILTER(OFFSET($C$3,0,0,$A$1),...
0
votes
0answers
11 views
Formula to reformat hours of operation
My current business hours are listed in 2 columns: "Open" and "Closed".
For example, open column would have this:
Mon.: 10:00 AM−6:00 PM
Wed.−Fri.: 10:00 AM−6:00 PM
Sat.: 10:00 AM-...
-2
votes
1answer
30 views
i can't find the problem to this formula
=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
0
votes
1answer
18 views
Combining Separate Scripts into One
I've been trying to figure out a solution to this for a few weeks & I think I'm at the home stretch, but having some trouble piecing it together. With great help, I have a script (below) that ...
0
votes
1answer
17 views
Multiple conditions in same cell google sheets
=IF(A1=5, "green apple","red apple")
Add to this new condition:
=IF(A2=5,"green apple","red apple")
Basically if A1 and A2 = 5 then "green apple", ...
0
votes
0answers
12 views
Using zone barcode scan in Google Sheets to tag barcodes based off the zone?
So I am making this test spreadsheet to figure out how to implement zone barcodes in my master inventory tracking sheet. I can't figure out how to make it work with Google Spreadsheet functions. ...
1
vote
2answers
24 views
Google sheets random number generation help
I'm trying to generate random numbers in an interval, x number of times.
Imagine rpg dice sintax, like:
3d6 means roll 3 times a dice of 6 sides. Or 5d100 means roll 5 dices of 100 sides.
If I was to ...
0
votes
1answer
10 views
How can I highlight the source cells in a formula (e.g. =SHEETA!$E$568 )
I have a sheet that makes extensive use of cells from another sheet with formulas, some of which have errors. The formulas look like:
=SHEETA!$E$568
Is there a way, or a script, that can parse the ...
0
votes
0answers
14 views
Advanced Array Formula Documentation / Multidimensional Arrays / Conditional Variable Ranges
I'm manually tracking games and wins every few days. Games goes up by 3-20 with each update, wins is about 50% of games. So data looks something like this:
Games Wins New Games New Wins
2800 ...
-1
votes
0answers
27 views
Import data from web is throwing an error
I have created a Google Sheets workbook to import data of mutual fund NAVs from the Net for my personal use. This is the address where the info is located, "https://www.amfiindia.com/spages/...
0
votes
1answer
11 views
Summarize values from matrix table
I have a matrix-like table like so:
1 2 3 4
---------
1| x y
2|
3| y
4|x y
I want to create a summary table like so but I'm lost as to which functions to utilize:
-----
1 2 x
1 3 y
3 4 ...
0
votes
0answers
7 views
Pivoting 3-dimensional data
I have this 3-dimensional data and would like to have a chart to
view each person's data from their category (Approach-Meet-Master).
Any help?
Approach Meet Master
UA1 ...
0
votes
1answer
26 views
Using a Cell as a "Variable" in Formulas
So basically, say I wanted to do something with the range A:Z, for example do =SORT(A:Z, ...). Now, I have to change the range for the formula
multiple times in multiple locations, and it gets ...
0
votes
0answers
12 views
Google Sheets Conditional Formatting for Expiration Dates with Colors
I need to create conditional formatting for expiration dates of gift certificates:
Gift certificates are valid for 60 days.
Cells should stay green between days 0-30 before expiration.
Cells should ...
-1
votes
1answer
18 views
How do I convert a minute number to human-readable format in google sheet? [duplicate]
Is there a way to format the following number (in minutes) to human readable text:
60 > 01:00:00
60.5 > 01:00:30
24 > 00:24:00
1.5 > 00:01:30
80 > 01:20:00
120 > 02:00:00
0
votes
0answers
16 views
The equation generated by Google Sheets is somehow different from what it actually is
So I made a graph that charts out data points in Google Sheets and wanted to have a polynomial (3) trendline to describe it, but the equation it gives for the label does not match what it says (it is ...
-1
votes
0answers
18 views
I can't seem to be able to Import a data value from a website
I can't seem to be able to Import a data value from a website.
Can Anyone help me?
The website is https://alternative.me/crypto/fear-and-greed-index/ and the value i'm trying to extract is the one in ...
0
votes
0answers
13 views
Google, Sheet, inventory / countif & unique function to lookup update lines
I'm working since few weeks with google sheets & encountered an issue, which i wasn't able to find a solution for.
I have a small inventory file with tabs for each received qty (received) , ...
-1
votes
1answer
32 views
vlookup and paste in App Script?
Not sure if this is even possible, but would anyone know if theres a way of using app script to move cell data to a specified area. Sort of a reverse Vlookup?
I want it to send a cell's data to an ...
0
votes
1answer
24 views
Google Sheets "Query" issue with rounding
I am using a formula of:
=INDEX(QUERY($I$19:$I$30,"select I where I >= " & rounddown(D$27,2) & " order by I limit 1",0),1)
In D27 I have a value of: 0.8190
In I19:I30 I ...
0
votes
1answer
18 views
How to emulate behavior or cmd + down arrow in Google Sheets Macros?
If there is a column of 10 full cells in a row, and you go to the top of the column and hit command + down arrow, it activates the lowest non-empty cell.
I tried to record a macro while using cmd + ...
0
votes
1answer
13 views
Numbers Not Correctly Formatted When New Row Added Via Integromat
I'm importing data into a Google Sheet via Integromat. Column A is a Date and Column B is a time. Both columns have been selected and formatted appropriately (Format > Number > Date and Format &...
0
votes
1answer
14 views
Google Sheets Combining Is Between and AND operator Conditional Formatting
my programming knowledge is not great and I need some help. I am trying to get conditional formatting to highlight any cell in a column that contains values between 13-16 AND in a different column (...
0
votes
1answer
14 views
When I input a number value in cell, can it auto format to show all numbers I input after decimal point
So 132.468 & -186.0152 & 167.03... I don't want to have click format button every time to adjust it to see the whole number.
0
votes
2answers
23 views
Is there a way to remove entries based on condition in =filter?
I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed ...
0
votes
2answers
15 views
Apply FILTER() to result of TRANSPOSE() with custom condition
I've got the following data in a sheet:
ColA ColB ColC ColD ... [lots of additional columns]
ItemA A B C
ItemB D E
ItemC F
ItemD G H
(table A)
I ...
0
votes
1answer
47 views
How do you utilize conditional formatting for times over multiple days in Google Sheets
I am trying to visually see when events are taking place on our site. I have a list of events, their start date/time and end date/time. I would like to create a visual such as a Gantt chart that shows ...
0
votes
1answer
36 views
ImportHTML Script at Bulk
With great help, this script is working to pull in the 2nd table on the below link. The table cannot be scraped with IMPORTHTML unlike the first table (Regular Season works, Playoffs doesn't).
https:/...
0
votes
1answer
35 views
Google Sheets Entries into Google Calendar in same date
I am trying to create full day event in google calendar using google sheet. I have used the following script and it is working fine with my sheet but it has added event in the previous date:
// ...
-1
votes
0answers
29 views
How to get Mastercard conversion rate?
https://www.mastercard.com/content/mastercardcom/global/en/personal/get-support/convert-currency.html
I want to get the conversion rate for a pair (0 commission, same day)
Mastercard's rate is ...
0
votes
0answers
22 views
Is there a way to import captions from youtube to google sheets? [closed]
I want to upload captions for every new video in my channel to Google Sheets and then to my site mrhack.io.
Ideally, there should be three columns in Google Sheets: Video title, Video URL, Video ...
0
votes
1answer
26 views
Reference "This Cell's Column" in Google Sheets
https://docs.google.com/spreadsheets/d/1LG7AgcumhfQ1ZV0Em_oVjdPdrLCP-5CWWrw0WV1oCnY/edit?usp=sharing
(We have to track 3 separate point totals for everyone who has participated in each game, game ...
-1
votes
0answers
29 views
Need help with xpath_query for importxml in Google Sheets [duplicate]
The IMPORTXML formula for Google Sheets is "=IMPORTXML(Url, xpath_query)".
For the webpage https://gocollect.com/comic/iron-man-282, I've been trying to extract the data under "Preview ...
0
votes
2answers
33 views
How to show a report that has "numbers in thousands" as full numbers?
If I have a lot of numbers in thousands and I don't want to use regexp to append to them three zeros to see the real number how can I proceed? I didn't found yet a formatting solution or any other ...
0
votes
0answers
18 views
Query formula does not update automatically
I am having an issue with queries.
I track spotify data and want to find the top song for each album so I use a formula something like this:
=query('album daily'!$V5:$Y11,"Select V,Y order by Y ...
0
votes
2answers
19 views
How to stop date reformatting of SPLIT() output?
I have content like "3 4 5; 5 6 7; 8 9 10" in a cell and I would like to split it by the semicolons so that I have "3 4 5", "5 6 7", and "8 9 10" in three cells....
-1
votes
1answer
65 views
Error while Importing Events from CSV file to Google Calendar
I was trying to collect the birthdays of my friends in a csv file and trying to create events that repeat yearly so that I could wish them. But when I tried to import that CSV file into Google ...
0
votes
1answer
11 views
Colour scale based on a changing value (Todays date - Entered Date) in google sheets
I'm trying to make a colour scale based on Today() between another entered date, basically a scale that starts green to yellow to red. I've attempted to try For green: =DAYS(TODAY(),$I2)=0, For Yellow ...
