Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor.

1
vote
1answer
16 views

Conditional formating if cell is a formula

I'm trying to have cells which value has been entered manually and cells that are the result of a formula differently, is there a way to do that?
1
vote
1answer
17 views

Calculate cost based on time increments

I am working with a fee sheet that goes up in $ increments based on time and I would like Google Sheets to do the math to calculate the cost based on the time entered into a cell. Example: a time ...
1
vote
2answers
25 views

Perform operation on specific column in filtered range, right after filtering

I have this formula in E2: =FILTER(A1:C20,B1:B20=2) and need to perform substitute like: spreadsheet here I'm hoping to do the formula in a single cell, without additional columns.
1
vote
3answers
33 views

Identify max value, divide others by half, then total all

This may be too specific of a formula, but I'm hoping there is a function or script that will accomplish it. I need a Google Sheet to identify the largest number in a row and divide all of the OTHER ...
0
votes
2answers
22 views

Google spreadsheet computation load distribution with multiple computers?

In a Google spreadsheet that performs a lot of intense calculation, does opening the sheet on multiple computers provide more CPU resources to spread out the computation load? The sheet contains big ...
0
votes
2answers
26 views

Copying cell values to AVERAGE function

I’m pulling Free Cash Flow Data from Morningstar to Google Spreadsheet. I have formula: =IMPORTREGEX("http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t="&B4,"Free ...
0
votes
2answers
62 views

ArrayFormula for conditional consequential sum-up?

I have a table which looks like this: And formula in cell B3: =IF(A3>5, B2, B2+1) and on every next row a corresponding variation of this formula. I would like to know how to create ArrayFormula ...
1
vote
1answer
19 views

Cumulative Sum of grouped values

I found it a bit hard to phrase this question, so here's a screenshot: I'm trying to create a timesheet for our employees and would like to have a cumulative sum function that sums up the number of ...
1
vote
1answer
25 views

Formula for filtering all duplicate data pairs in Google Sheets

I'm making a table to check duplicate data. Where data that wants to be duplicated is in the "Sampel1" column and the duplicate checking results are in the "Status" column. The formula that I have ...
0
votes
1answer
38 views

Translate UrlFetchApp Response HTML To Google Sheet

I'm new to working with APIs/CURL and trying to figure out how to get a response (not response code) into a sheet. Here's my code: function myFunction() { var url = "https://platform.quip.com/1/...
1
vote
2answers
35 views

Jump to today's date when opened

I have found a similar question here: How to make Google Sheet jump to today's column when opened? I want to do a similar thing, but I have the dates in row 5 not in the column. I tried several ...
-1
votes
0answers
11 views

How to find a row and update a specific cell depending on my input

I want to find a whole row data and update a specific cell in that row, value is depending my input. Here I have a data, I want to search the row based on Site column, and I need to change the some ...
1
vote
1answer
27 views

Get URL from web page using IMPORTXML

Though I am new to web scraping, but I have a hand on experience of web scraping using VBA (Excel) I am facing the following problem: I have tried all methods (ImportXML, ImportHTMP, ImportData, etc.)...
0
votes
2answers
18 views

Flag rows that are duplicates in two columns

My question is similar to this one, but adds a little more complexity. How can I flag duplicate entries in a Google Sheets column unless the duplicates have different values in another column? For ...
0
votes
1answer
25 views

Google Script Range Error - Owner Range?

I've been playing around with Google Scripts and I'm trying to pull some data into a sheet. It all seems to check out except one line where I get a range error, I know (or I think I do) that it has ...
0
votes
0answers
9 views

The “sheet” tabs at the bottom of my form are oversized along with the “find in sheets” window. How do I resize them back to normal?

The "sheet" tabs at the bottom of my form are oversized along with the window for the "find in sheets" (Ctrl-F window). How do I resize them back to normal? My document itself is fine, just the ...
-1
votes
1answer
15 views

Query is not bringing in data when column is between two dates

I am using the correct formula which has worked on other sheets: =QUERY(Patients!$A:$K,"SELECT * WHERE F > date '"&TEXT(B2,"yyyy-mm-dd")&"' and A <= date '"&TEXT(C2,"yyyy-mm-dd")&...
2
votes
1answer
11 views

IF Cell = True, then retrieve (separate) Cell Value

Cells: T2 = Price to Pay U2 = (Tick box) V2 = =if(U2=True, "Paid", "To Pay") W2 = empty What I want to do is be able to tick U2 and have W2 contain the value of T2. V2 works, so it does not ...
0
votes
1answer
43 views

Can you auto update the range for a filter in Google Sheets?

I have a Google Sheet document with many rows in it. I have several filters set up to view different collections of rows. The problem is, this sheet has new rows automatically added to it several ...
1
vote
1answer
22 views

MIN and SUM functions in Google Sheets for Time

I'm trying to use the MIN and SUM function but the answer always comes back with either 0, ERROR or !DIV/0. The format I want to have for my times are for example: 0.03, 10.56, 1:13.00. =MIN example:...
0
votes
1answer
18 views

How to output end task time base on start time, duration and working time in Google Sheets

I'm using a spreadsheet to perform calculations and I'd like to make a task management file. For example: Working Time: 8:00 ~ 17:00 Lunch Break: 12:00 ~ 13:00 +- Start Time -+- Duration -+- End ...
1
vote
1answer
57 views

IMAGE from Google Drive in Google Sheets [duplicate]

I have the following image on a cell in my Google Sheets: https://drive.google.com/open?id=1C9KL2vEhnZOVdCe2qUG5MZomw3jpvCiX When I use the formula =IMAGE(A1), the cell with the image stays white... ...
1
vote
1answer
2k views

How can I change the Google Docs (Sheets, Slides) user interface design?

I have several Google accounts. I noticed that the user interface of G Suite Apps in my new account is different from the one in my old account. Could you please tell whether I can choose it and how I ...
1
vote
1answer
10 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
0
votes
1answer
35 views

How to print the first modified date of a cell

I would like to make a script that works like this script to print a timestamp when a cell was last updated but in this case I want to return when it was first updated, not last. Here is one attempt:...
1
vote
3answers
52 views

SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM

Currently, I have a spreadsheet which has a number of customer sheets, each with a pivot table showing the month in the row I and the total income for each month in row L. On a separate sheet I have ...
-2
votes
0answers
30 views

Using OFFSET to skip columns with a certain header in Google Sheets

So this is a tad more complicated than the title suggests so try to follow along: On this sheet, I'm trying to get cells D4:G4 to show the most recent 4 weeks with data on my spreadsheet. My formula ...
0
votes
2answers
16 views

Defining Value to Numbers and Text and then Adding them

I am comparing apartments and I have columns for features like bedroom, full bath, half bath, rent, pet friendly, balcony, laundry, utilities, parking, and bus access. Some of these have numbers like ...
0
votes
2answers
24 views

Insert weight in kgs AND lbs in one cell?

Right now, a cell is showing the value: 10 I want it to show: 10 kg or 22 lbs And I want that to happen in the same cell. Also, that 10 is also a result of a calculation =B2-80 I ended up using ...
0
votes
1answer
13 views

Empty cell reference when “Loading…”

I want to do a calculation with imported data from an HTML link. The data is imported every minute. There is a loading time and this ensures that the cells are empty during loading. Because my formula ...
0
votes
1answer
35 views

Dynamically creating chart in Google Sheets

I'm looking for a way of automatically generating a graph based on a constantly expanding dataset. The number of columns is constantly expanding (a program create a new column with new datasets for ...
0
votes
1answer
21 views

Cell values ​do not update

I want to update every minute a cell with data from a HTML link. The problem is that the value does not update unless I manually empty the cell or make changes to the script. I tried to add NOW() to ...
2
votes
1answer
36 views

Current value of compound interest

How can I calculate the current value of a sum deposited in a past date, with a fixed interest rate? For example, if $1,000 were deposited on 1/1/2000 in an account which pays a 3% annual interest ...
0
votes
2answers
17 views

How to round float to bigger integer?

In Google Sheet, we can use ROUND() to round float to the nearest. like 5.2 = 5 5.5 = 6. What if I want it: 5.2 = 6 5.5 = 6. If the number exceeds by a decimal, then rounded off to +1. Any idea?
0
votes
1answer
20 views

Move ROW upon selecting a specific option from drop down menu

I am looking to use Google Sheets to keep track of my jobs. I have a spreadsheet set up so the status of a job is indicated by a dropdown menu. When a job is completed I would select "Closed" from the ...
0
votes
1answer
10 views

Can I use conditional formatting to highlight a cell for dates less than 30 days away?

I have a large spreadsheet where I need to keep track of coming expiration dates and I would like dates which are within 30 days from today's date OR which have already passed to be highlighted in red....
1
vote
2answers
48 views

Conditional Formatting - Custom Formula - Text Contains

I have dates in column B that include weekdays in the following format: DDD, MMM DD, YYY (For example, Sat, Jan 19, 2019). I want each row to auto format to a specific background colour based on the ...
1
vote
1answer
24 views

Convert inputted time duration to minutes:seconds [duplicate]

I'm working on this spreadsheet, and I wish to convert the data in columns C, D, and E from hours and minutes to minutes and seconds. For example, an inputted 7:08 turned into 07:08:00.000 (hours and ...
0
votes
1answer
26 views

Is it possible to use checkboxes to determine what data is in a list?

This is hobby related, so feel free to ignore. I want to learn how to use Google sheets, and am doing so by building a partial DnD character sheet, piece by piece. I am trying not to use scripts as ...
1
vote
1answer
18 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
1
vote
2answers
22 views

How to check if the 1st letter in a cell is UPPERCASE

I'm trying to find out if the 1st letter in a cell is UPPERCASE. I know how to convert case using PROPER(), UPPER(), and LOWER(), but not how to detect case. The following checks every word in the ...
0
votes
2answers
16 views

COUNTIF to display which results meet criteria

I'm working on a spreadsheet, where I'm using COUNTIF to check the number of times a sheet range meets a criterion. This works. I'd like to see which cells (their contents/locations) which met the ...
1
vote
1answer
31 views

Move cursor to cell in all sheets

I have the following script: function setActiveCells() { var wb = SpreadsheetApp.getActiveSpreadsheet(); var ws_names = wb.getRangeByName('an_sheets').getValues(); var ws_cells = wb....
0
votes
0answers
8 views

How to prevent series from changing color when series are removed from the chart/graph?

I have a graph, similar to the one in this gif: When I uncheck different series, removing them from the data that is then read by the graph, the colors shift. I believe the issue is that the color ...
0
votes
0answers
14 views

Auto-Sort script which excludes formula if ascending False

I am looking to exclude formulas or blank cells when I am running my auto sort. I used the following... It sorts fine, but I wanted the numbers to be ascending: False. When I change this, it takes ...
0
votes
0answers
13 views

Google Sheets [Script]: Add new row to the top when previous one is filled up [duplicate]

I have a spreadsheet where I record info by rows and it's troublesome to scroll down to type out in a new row. I'm looking for a script to help me add new rows. What happens is that row 2 is blank (...
1
vote
1answer
21 views

Can results of ARRAYFORMULA be used as values in an annotated timeline?

I am trying to create an annotated timeline with the following data, which exists on another sheet: I am using an ARRAYFORMULA to retrieve the text. I want to display these values as an annotated ...
1
vote
2answers
16 views

Formula for PROPER Case with Apostrophe

I'm using Google Sheets glued together (not be me) from multiple sources where some of the names are in all caps. In trying to clean web-ready content, I have good success with the PROPER() function ...
0
votes
2answers
16 views

How to count cells in a row while skipping specific columns?

Basically, I need to count non-blank cells in a row and I need to skip certain columns/cells in that row. I'm sure this is simple combination of functions but I can't seem to find a solution to this.
0
votes
1answer
17 views

Divide number by WEEKNUM to get the rate per remaining weeks of this year

I am trying to divide a number by the total remaining number of weeks and have it calculate as the weeks' progress. I am using the formula =D58/today(WEEKNUM(1/5/2019,0)), but I keep getting an #N/A ...