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
0answers
33 views

Difficulty appending to cell in Google Sheets

My goal is to automate making a calendar on Google Sheets. I used =DATE(C1,A1,B1) to make a date and then each following cell, I added 1 to the previous cell. However, now I want to also automate ...
0
votes
0answers
15 views

Data Validation Custom Formula

I'm trying to create a drop-down menu that is dependent on a previous cell in a row. The data coming in from a DB is M or F. Male or female. Then from what is displayed there I want the next column ...
0
votes
1answer
31 views

Arrayformula and Sum queries. Possible alternatives

I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet Basically in the sheet "Substitutions" all absences and substituions are stored record by record: ...
0
votes
0answers
24 views

Is duplication of Structure and Behaviour a 'fact of life' in the world of Google Sheets. Is it possible to reduce that duplication further

Please note, I am not asking how to remove duplicates from a column of data. We are transitioning to a relational database system to store data. But, in the mean time we are using Google Sheets to ...
0
votes
1answer
23 views

Google Sheets - select highest value for each unique label

I have a simple spreadsheet which is tracking timeseries data for several users: Time | User | info 00:01| bob | hello 00:03| sue | hello 00:05| bob | goodbye 00:10| sue | hello again I would ...
0
votes
0answers
48 views

How to format a negative number red within a mixed formula result

We use the following formula: ="Stu/W: " & TEXT((A$2/B2),"[Red]#,###,##0.00") &"€" and wish to have the resulting negative currency numbers colored red and positive blue. Unfortunately ...
1
vote
1answer
25 views

Conditional Formatting with Multiple Conditions Weekday and Time

I'm working with Google Sheets and want to highlight cells in long date format (e.g. 11/13/2018 21:00:00) that are Tuesday and 21:00:00. Here is my attempt: =AND((weekday(C4:C124)=3),(EQ(RIGHT(C4:...
0
votes
0answers
37 views

How to calculate DATE DIFFERENCE in GOOGLE Sheets?

I have a google sheet with NHL schedules (hockey). I want to calculate the difference between two games of a specific team. Example: Nashville played on 2018-11-12 and last game before was 2018-11-10 ...
0
votes
0answers
12 views

Dates entire week Google Spreadsheet with script

I'm building a tracker and I want different pages to represent different weeks, but I don't want to mannualy put in all the dates of the entire week, every week. Is there a script that I can use that ...
0
votes
0answers
10 views

How to highlight a cell in column A if the text in column B matches text in a list on another tab?

I have a Google Sheet set up with place names in column B as below. I have another tab on the same spreadsheet with a list that can change from time to time depending on which stores needs items ...
0
votes
0answers
22 views

Google sheets auto add calculations to rows from Google Forms

I have a sheet 'Form responses 1' that is autofilled from a form. I was told to carry out all calculations on a seperate sheet to avoid any issues. So in a seperate sheet I am pulling in the date, ...
0
votes
0answers
13 views

Multi url parsing in G Sheet

Sorry i am just a newbee in stackexchange, its great solution place, i have followed Question Edited I have got to accumulate the data from 7 to 10 urls which are rss feeds, xml feed and from json ...
0
votes
0answers
10 views

How to stop Google Docs from accidentally switching user accounts

I have been using Google Docs and Google Sheets for years together with the co-director of our company - switching between her account and my account when needed. Within the last couple of months I've ...
-1
votes
0answers
9 views

Google Sheets: cursor staying in cell

By comparison between Excel where when you press keys Ctrl+Enter, the cursor doesn't move from the current cell, in Google Sheets there's no such option. Well I think there's not such option. Can you ...
0
votes
0answers
11 views

How do you get values from column C and D if a value in column B exists

I have a yearly budget spreadsheet on google sheets that has: Date in column A Name of shop in column B How much I spent there in column C Loyalty points collected in column D The list is updated ...
-1
votes
1answer
14 views

Setting ImportRange on a delay

Is it possible to set a timer or delay for the importrange function on Google Sheets?
0
votes
0answers
22 views

How to summarize multiple tables

I have a 13 tables that are the 4 week schedules for the coming year. I would like to summarize them to show how many of each type of shift each employee has been scheduled for. Here is an example: ...
-1
votes
0answers
22 views

Create new worksheets from a list of names in Google

I have a list of names that I would like to create worksheets for, with their name as the Title of the Worksheet. Is there a script that I can use to make this happen?
0
votes
1answer
15 views

Multiplication Sums across cells?

I know this isn't very professional but i'm very very new to google sheets and spreadsheets in general. So if I know that Tier 4.3 leather is worth 6 points in the contest, someone brings me 739 of ...
0
votes
0answers
10 views

Next lowest value query

New to queries. E8 = calculated value based on manually inputted data in B4 D2:D13 = contains set numerical values ranging from .00722 to .003608 from lowest to highest B7 = query field that ...
0
votes
1answer
9 views

How to get data from a different sheet based one the adjacent sheet in Google Sheets?

How would I change the sheet name in B3 when the name is changed in A3? Could someone please assist me with this?
0
votes
1answer
19 views

Copy data to new column in Google Sheets

I have a sheet with IMPORTHTML that I want to copy data from one column to a new column daily. function saveData() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet....
0
votes
1answer
17 views

Stop Google Sheets formatting text as URL

When I type "product.name" or "product.id" into a cell, it's auto–formatted as a URL (different colour, underlined, hover shows it as an external link). But it's not a URL, it's plain text. Similar ...
1
vote
2answers
18 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
20 views

Sum of rows until a condition changes

I have a Google Sheet with the following format: A B C D 10-12-18 17.36 Start 10-12-18 17.49 Stop 13 10-12-18 17.49 Start 10-12-18 17.51 Stop 2 ...
0
votes
1answer
14 views

Why are sheet Filter Views not updating for new rows?

I have a sheet with two filter views, which apply filter on one column and sort on another. The sheet is populated from the API nightly - it has all worked great but today I noticed things in the ...
0
votes
0answers
7 views

Formula to compare the last two values in a row

I have a spreadsheet with 12 columns (B2:M2), that represent the months of the year. The columns of each row will either be blank, or contain a numeric value for a given month. What I'm trying to do,...
1
vote
0answers
7 views

Dynamic Sized Image in Google Sheets For Printing Based On Cell

I've made a calculator with Google Sheets to make it easier for Olympic shooters to train at home. The data that it produces is very helpful, but I would like to make it easier for the users to print ...
0
votes
1answer
30 views

Formula for go Google Sheet1 column K

I need a function for column K of Google 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. ...
1
vote
2answers
20 views

Restrict ImportRange to only visible rows

I need to restrict the ImportRange function below to show only visible rows as some of the rows are filtered and some of the rows are grouped. =importrange("WorkbookID","Sheet1!A2:K13285")
1
vote
0answers
14 views

How to Get Data from Multiple Cells and Combine into a Comment in another cell

I have a spreadsheet of orders (rows) that has room for 70 products (columns). I would like for a script to find every cell that has a value (0's are blank) and then write that value (this is the qty ...
1
vote
1answer
23 views

Sheet Specific Scripts

I'm currently using the below script to timestamp when an edit is made to column 7 on a sheet named "Sheet 1". function onEdit(e) { if ([7].indexOf(e.range.columnStart) != -1) { e.range.offset(...
0
votes
1answer
23 views

Automatically Create a New Copy of a Public Google Sheet

Our company just created a new template for website planning. It is available as a Google sheet. We've also added instruction on how to create a copy for themselves. Like open the document, Go to File ...
1
vote
2answers
16 views

3 Conditions IFS not behaving as expected

I'm building a commission tracker at my work and I need to search a specific cell and produce 1 of 3 values (in a different cell) based on the text string that the cell holds. Here is the logic: If ...
1
vote
1answer
22 views

Merge array into column row by row

I'm trying to do this without code. I have an array of 4 columns with an undefined amount of rows, and I want to merge them into 1 column, but not by appending columns (so I can't use {A1:A;B1:C1;C;.....
0
votes
1answer
18 views

Sum the value of the cells above going up until blank cell

I want to sum the cells above of the current one until an empty cell is found. For instance, in the image below, in the summary row, I want to sum the revenue from the orange cell (the red one) going ...
0
votes
2answers
17 views

Google Sheets Lookup For Nth Value

I am working on a spreadsheet that seeks to collate scores to a team. There is an input table where team and score start next to each other (see the example sheet linked below) into a table with each ...
1
vote
1answer
40 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
3answers
53 views

How to analyze a time tracking sheet with Google Sheets?

I have a simple time tracking set up with Tasker on my phone that creates a new line every time I connect to my work Wifi. I upload this csv file regularly to Google Drive and import it to a Google ...
0
votes
1answer
11 views

How to group certain data in a chart in Google Sheets

I'm creating a chart of a number of items (website traffic sources) and want to find a way to merge any rows that contain 'Facebook' in the chart. Here's the raw data: (direct) 67 192.com 0 bing ...
0
votes
1answer
14 views

Adding a cell in a sheet with a specific column number requirement that pushes cells to the beginning of the next line

I have a sheet that I need to only be 5 columns. I use this as a teaching calendar. Sometimes I need to add a cell in the middle if there is an activity that will add an extra day into my calendar (...
0
votes
2answers
21 views

Google Sheets Increase month by 1 but leave everything else the same

I am using Google Sheets and have 700+ rows that have an assortment of dates but the month is the same on all of them (monthly billing cycle) each month I am having to manually go through each line ...
2
votes
1answer
56 views

Can you specify an accout to use with Google's “.new” doc creation URL?

I am usually logged in to multiple Google accounts, and I have been using links like these to create sheets (docs, etc.) in the respective accounts: https://docs.google.com/spreadsheets**/u/0**/...
0
votes
1answer
13 views

How do I share a Google Sheets document and restrict editing to only one column and yet hide the document history from them too?

I have a Google Sheets document that I want to share to many users. However, they must only be able to edit one column. This part seems fine, however, I have numerous other hidden tabs for the purpose ...
0
votes
1answer
33 views

How to set cell value based on 2 conditions of 2 other cells?

So, I've got a spreadsheet where people enter in values into columns A through H with I being a checkbox. On another sheet, it keeps a running total of all of the values of column E based on the ...
0
votes
0answers
23 views

Selecting relevant data in a large sample Google Sheets

I have 8 sheets with data from an experiment, each sheet has 2500 measurements. I want to select only 50 measures from each sheet to make a relevant analysis. Considering the values measured in each ...
0
votes
1answer
25 views

Count how many times in a row a cell does not equal the cell above it

I want to count how many cells in a range of a row (G2:N2) are not equal to the cell above it, i.e.: G2 != G1, F2 != F1, H2 != H1, ... , N2 != N1 And then also be able to drag that down, so each row ...
0
votes
1answer
11 views

Auto update field exclude header row

In Google Sheets I have a column that needs to be updated with the current date & time every time another cell in the same row is updated, but skip the top row which is the header. I found this ...
1
vote
3answers
66 views

How can I apply conditional formatting to a Google Sheet to show three checkboxes minimum are checked?

I work in a school where I used a Google Sheet to track student attendance. If a student is late or absent, I check a checkbox. I would like to find a formula to automatically turn a line red if ...
0
votes
1answer
35 views

In Google sheets, how to calculate elapsed duration between two cells, each containing date and time and display as days and hours

There is a built-in duration format, but Elapsed days is not an option. I want to display "2 days, 3 hours"