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.

Filter by
Sorted by
Tagged with
0
votes
0answers
8 views

“Exception: Service Spreadsheets failed while accessing document with id” when using insertCells

I have a confusing problem. I'm using GAS to shift several cells down by one. Worked fine for a while, then I started getting this error message: Exception: Service Spreadsheets failed while ...
0
votes
0answers
8 views

Automating Repeating Sections to rows in Google Sheets? any new ways to handle this?

I found 1 answer from over 3yrs ago and I'm hoping by now there might be other options? I have a Cognito Form and the responses I would like to go to Google Sheets. Normally, a simple zap to bring in ...
2
votes
2answers
2k views

Conditional formatting with color scale for many rows independently once

I create reports with different cohort tables. Each row of a table must have conditional formatting with color scale (depending only on current row values, i.e. regardless of other rows min/max values)...
0
votes
1answer
10 views

Sum of products in a single row

Here is the general structure of my spreadsheet: Start End Total X1 Y1 X2 Y2 X3 Y3 ... Xn Yn 09:00 10:00 1560 20 15 10 30 12 80 Total = X1 * Y1 + X2 * Y2 + X3 * Y3 ... Xn + Yn The SUMPRODUCT ...
0
votes
1answer
19 views

Referencing a calculated value in another calculated value

Here's the general structure of my spreadsheet: Start End Count Duration Hours Count/Hour 09:00 10:00 10 01:00 1.00 10 Duration = End - Start Hours = TIMEVALUE(Duration) * 24 Count/...
2
votes
1answer
22 views

Sums with Same Value - Follow-up Question

This is a follow-up to these questions and information. Thank you Jacob and Kowser for the formulae you shared many years ago! The information you shared is almost exactly what I need to simplify a ...
3
votes
4answers
2k views

Increase 1 increment every x rows on Google Sheets

Basically, I just want to automatically increase the week number (on column A), and have it correspond with every new Monday (in Column B). I have been trying to click and drag, hoping it would ...
-1
votes
1answer
12 views

Is there a non-coded way to pull last edit user and date into a cell?

Similar to the information as the solution found here. (Is there a quick way to see when a cell in a Google Sheets was last edited?) I would like to populate a cell value with the User who edited the ...
0
votes
2answers
1k views

Applying IMPORTRANGE formula in Google Sheets between tabs

I have one Google sheet having three tabs, Sheet 1, Sheet 2, Sheet 3. I have another tab having the name "Sheet 4" in which I want to import all data from these three tabs, but the problem is data in ...
1
vote
1answer
90 views

Can I record the users within a Google sheet?

I'd like to have an auto-generated list of users with timestamps within a spreadsheet, tabs, or if possible specific cells. Ideally, once a cell has been altered, I'd like another cell to record the ...
2
votes
1answer
24 views

Using multiple IF and OR statements with calculations

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. ...
1
vote
1answer
7k views

Using AGGREGATE formula on Google Sheets - Finding the correct result based on 3 columns of criteria

It took me a long time to get this working on Excel, and just as I managed to do it, my company changed over to Google Sheets! I work for a packaging company, and we sell hundreds of different boxes ...
0
votes
1answer
39 views

How to organize and add multiple tags to a list?

I'm trying to create a sort of "goal bank" and I'd like to organize it so that you can search for goals based on various categories (i.e. diagnoses [autism, mental health, etc.], domains [social ...
0
votes
1answer
1k views

How to use calculated fields to pull up “Grand Total” in Google Sheets?

I have a grand total value that I want to use in my calculated field. Instead of using a specific number like =sum(impr./919), I want something like =sum(impr./"grand total") and that the grand total ...
-2
votes
0answers
12 views

Google Sheets, formula for spreading income to expenses?

I'm making my income google sheet, I have a table for income, and a table for expenses. How to: When I write my weekly income I want it automatically spread throughout my expenses, and show difference ...
1
vote
1answer
29 views

How do I copy formatted formula results in Google Sheets?

I have formula cells outputting numerical results that are then formatted with a " km" (kilometres) post-fix. For example, the formula returns 21.4, which is then formatted as 21,4 km. I ...
2
votes
1answer
26 views

How do I input a current balance in a cell from a running balance that will be updated frequently?

I know I'm not accurately explaining that but here's a sample spreadsheet of what I mean:
-1
votes
0answers
11 views

Google Scripts Sheets Conditional Sorting

I just recently discovered Google Scripts, and I was wondering if there was a way to sort by a function. For example, if the values in column B were in the range of all real numbers, would there be a ...
-1
votes
0answers
10 views

Short number format in Google Sheets; 1,024 → 1K, 1,816,724 → 1.8M include T

Short number format in Google Sheets; 1,024 → 1K, 1,816,724 → 1.8M I saw answers just until B, but couldn't figure out the formula please let me know how to add T as well Thank you
0
votes
1answer
749 views

Auto-populate cells based on previous cell values in Google Sheets

I have two columns with information. Values in Column B have been manually added to match the values in Column A. Is it possible to have a function that auto-populates column B with the right values ...
0
votes
0answers
20 views

How to change the sum of different currencies based on a drop-down value (currency)

I'm trying to achieve that when you select 'USD' from the dropdown e.g. it automatically calculate the two tickers with different currency into the currency you select. What I would like was that the ...
0
votes
1answer
216 views

SUMPRODUCT inside ARRAYFORMULA in Google Sheets

I have a SUMPRODUCT equation that works fine on one row: =SUMPRODUCT(O4:O10-O3:O9,E2-P4:P10,N(E2>P4:P10)) However, instead of copying it for each E row I want to use ARRAYFORMULA to automatically ...
0
votes
2answers
118 views

A formula for importing specific data into another sheet

I need a function for column K of Sheet1, that says: "If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2" ...
19
votes
4answers
17k views

Short number format in Google Sheets; 1,024 → 1K, 1,816,724 → 1.8M

Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method? I would expect to enter a number like: ...
1
vote
1answer
20 views

How to copy and paste a formula changing only the cell references made on this sheet

How can I copy and paste a formula changing only the cell references made on the active sheet and leave references to ranges on another sheet as is?
0
votes
1answer
22 views

Conditional formatting with custom formula

I have pretty much zero experience with spreadsheets but I've got what I think is a simple task. I've been keep a log of how I spend my time. I've codified the language so I can easily grab the ...
0
votes
1answer
3k views

How can you *turn off* the new “Show Edit History” feature in Google Sheets?

I turned on "Show Edit History" for one cell in a Google Sheet and now I want to turn it off for that one cell. I can't see any way to do so. If I right-click on the cell I see "Show Edit History" ...
0
votes
1answer
17 views

Histogram for scattered dates (some days have no row, they should still show up, with height zero)

I have this data (as you can see I did zero pushups on the 21st/23rd/24th): Date Pushups 9/20/2020 150 9/22/2020 100 9/25/2020 200 What is the easiest way to make a histogram with this data? ...
0
votes
1answer
28 views

How to get the SUM total value to appear in cell vs the formula?

So I have created a spreadsheet to add cells =SUM(B2:B20) ... When I hit enter the sum value should be in the cell I created the formula but instead, I have just the formula after hitting enter. How ...
0
votes
1answer
14 views

How do I format a column of 3 and 4 digit numbers to add a 0 in front of any 3 digit numbers so they are all in 4 digit format? Google sheets

How do I format a column of 3 and 4 digit numbers to add a 0 in front of any 3 digit numbers so they are all in 4 digit format? Google sheets I have a time function formula I'm applying to a series of ...
1
vote
1answer
344 views

Cumulative Percentage Subtraction Google Sheets

I have two columns in Google Sheets, where A = score and B = number of absences. After each absence, A will decrease cumulatively by 8%. For example, if A=100 and B=2, then the answer should be 79.28 ...
0
votes
1answer
292 views

Filtering a QUERY using MATCH - how to pass column value into function? Or is there a better way?

I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet. I can use QUERY to get all the data available, but I only want to return ...
0
votes
2answers
19 views

What is wrong with this Google Sheet formula?

I'm working on a Google Sheet to use for stock trading and I want to have a cell that checks (W% equal or higher than L%) AND (L% equal or lower than 33%). I thought this formula would work but it ...
0
votes
1answer
33 views

Match mixed data

In one column there are mixed sections, which are to be matched with data from other sheet. But as the sections format is different in entire column, not able to match I have 2 sheets in which i ...
0
votes
1answer
24 views

Any way to duplicate a formula that needs individual relatively absolute references?

I need to duplicate a formula down for many rows. However each one needs to be an absolute reference, referencing a specific cell in that individual row. I can't just fill down the original formula ...
17
votes
2answers
38k views

Get value of cell on the left of current one

Which is the correct formula to get the value of cell on the left of current one. I've tried =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1) but it populates the error of circular dependency.
1
vote
2answers
144 views

How to count the number of empty rows since the last entry in a column?

I'm trying to get Google Sheets to tell me how many rows it has been since the last "1" was present in column A (see example table below). How do I get Google Sheets to output column B? An ...
0
votes
1answer
19 views

How to query a dataset while calculating cumulative values (and filtering by thresholds)

I was hoping someone can help optimize this spreadsheet, where I need to QUERY a dataset while filtering by the cumulative sum of a column by certain thresholds. Here's the spreadsheet if you want to ...
1
vote
1answer
42 views

Average the last N values in a Sheets column using Arrayformula

Experts, I need your help... I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't. Let me ...
0
votes
1answer
29 views

Use Text in cell as formula in Google Sheets

I am working on a way of building out a formula rather than a bunch of if/then statements. Is there a way I can convert the text from one cell and use that text as the formula of another? Example: A1 ...
1
vote
1answer
78 views

How to concatenate several “filled” time slots in a spreadsheet and then check if they completely fill a “main” time slot?

For each row that represents a day of the month (month day 1, month day 2, month day 3 and so on...), I need this spreadsheet to answer NO at column C if no employee will be in the office for at least ...
2
votes
2answers
653 views

How can I quickly change the color of text in a cell in Google Sheets?

How can I quickly, with either a single or double click, assign a color to the text in a cell in Google Sheets? In order to quickly determine the status of certain pieces of data in a spreadsheet I ...
0
votes
2answers
71 views

How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
0
votes
0answers
10 views

Need play button to mp3 file to play immediately for competition in Google Sheets

I'm creating a score sheet for an outdoor competition. I'll be scoring competition using a tablet. I've got the formulas for the sheet figured out, but having issues with a simple button to start a ...
0
votes
1answer
92 views

Average first 3 values excluding empty cells in google sheets

Trying to find the average of the first 3 values in the column. I'm already running a query to find the average data within the last 3 days =average(query({$A$10:$A$107,$E$10:$E$107},"Select avg(...
3
votes
2answers
3k views

Does Google Sheets allow moving a chart to its own sheet?

I know that Google Sheets used to allow moving a chart to its own worksheet so that nothing else is in that sheet. All the instructions I've found for how to do so are no longer relevant to the most ...
1
vote
0answers
26 views

Sheets - display ISO8601 string as localized date

In Google Sheets, is there a way to have a cell display its value as a localized date (and time) but actually still have and accept its value as an ISO8601 date string? The use case is that I have ...
0
votes
2answers
24 views

Google spreadsheets - sheet cells are not clickable

A very strange issue... I cannot click the cells with my mouse. The mouse does move and operates. It can even work the menu. But not the cells. However, using the keyboard I do manage to manipulate ...
0
votes
2answers
1k views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
1answer
268 views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...

1
2 3 4 5
126