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.
0
votes
0answers
10 views
Blank Redundant Month
I have a large list of days and quantities that I wanted to chart so we could see how many of something we sold on a specific day.
Give this goes back 600 days, there is a lot of data so I have been ...
1
vote
1answer
4 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?
Thanks
1
vote
1answer
13 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 ...
0
votes
1answer
11 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
2answers
21 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
13 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? Does the account sign in ...
0
votes
2answers
16 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
0answers
23 views
ArrayFormula for conditional consequential sum-up? [on hold]
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 ...
0
votes
1answer
9 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
17 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 ...
-1
votes
0answers
7 views
Format an entire named range
I want to make sure I can see that a named range is including all the data I want at a glance, even when I'm not looking at the named ranges dialog box. Is there a way to use conditional formatting to ...
0
votes
0answers
21 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
1answer
12 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
12 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
0answers
10 views
Google Sheets - How to concatenate 2 rows if the date is the same?
I have a Google Sheet that has multiple columns and the date row is on the A Column.
The client uses a form to input data in the morning and the evening, creating two entries.
How do I create a ...
0
votes
2answers
12 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
0answers
18 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
8 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
9 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
7 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
12 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
17 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
16 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
17 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
263 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 ...
1
vote
1answer
9 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
27 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
44 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
24 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
1answer
7 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
19 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
9 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
26 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
15 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
29 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
15 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
16 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
24 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
12 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
20 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
13 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
14 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
11 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
15 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
7 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
12 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 ...
