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. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

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

Google sheets renaming and grouping like entries

I want to create responses "nuts", "dairy", and "nuts and dairy" with no double-ups. I only want the three responses but my data currently shows about 20, each with additional info I don't actually ...
0
votes
0answers
18 views

How do I combine 2 IF conditions in Google Sheets?

I am trying to combine the 2 IF statements below into 1. Can you tell me the combined syntax when trying to combine the 2? =IF(AND(C5="Non-eng",P5>=2,N5>=0),"Met","Not Met") =IF(AND(C5="Eng",N5&...
-1
votes
0answers
22 views

Google Sheets cell formatting

I'm trying to save data in cells containing a dropdown list that can be edited by specific people. For example: John can edit Sheet 1 cell A3, Barry cell B3, Joe cell E3, etc. After the last person is ...
0
votes
0answers
25 views

Pull data for different sheet and keep formulas in new rows

I'm trying to accomplish 2 things at once. I have 2 pages with customer information, one page being contact info and another being CRM information based on those customers. I'm trying to populate ...
2
votes
1answer
37 views

How to count Hours and Minutes in one cell

I want to count # of hours worked by subtracting the Start & end time in 1 cell (as per my comp template) ( 9am - 6pm) .( No I can't alter the template to put start & end in different cells/...
0
votes
2answers
33 views

Google Sheets interpreting dates from CSV in a weird way

I'm opening a csv report in Google Sheets. The Google Drive preview shows the dates like this: When I open this file in Google Sheets the dates now look like this: And if I try and convert the date ...
0
votes
1answer
14 views

How to prevent accidentally deletion of spreadsheets

I am using google drive sheets every day and today i deleted a folder by mistake so please share me the ways how to prevent the sheet deleting it
0
votes
0answers
16 views

Import button greyed out in Google Sheets menu for unknown reason

I'm trying to import a .csv file into my Google Sheet like I've done myriad times. However, occasionally, the Import button in the menu is greyed out (see screenshot). Reloading the document doesn't ...
0
votes
0answers
13 views

2 questions about multiple groups of rows

there's two small things I'd like to do, but can't seem to wrap my head around. I've succeeded before but in an "unsophisticated way" and I know there must be a better way to do this. I have a ...
0
votes
1answer
25 views

How to make Checkbox=TRUE if SUM of cells in its row are more than a specific value

I have a row of numbers and those in columns C through F are added together in G. I want H to be a checkbox that is TRUE if the SUM in G is greater than 29. I think I use the SUMIF formula but couldn'...
0
votes
0answers
7 views

Randomly distribute the sum of a number between cells in Google Sheets

I'm planning out a Kickstarter campaign and I'm making a sample tier list in google sheets to see if I can get the funding needed with minimal backing. I've randomly added in numbers to the estimated ...
1
vote
1answer
30 views

How can I trim white-space and replace trimmed space with a semicolon?

I am working on a project where there are hundreds of eCommerce product features separated by white-space. The number of spaces differs from row to row. I need to reformat all these cells with ...
1
vote
1answer
30 views

Google Sheets - Identify if one of many matches any in an array

I am trying to determine if an array of values in Google Sheets has any matches within another array of values. The data set is constantly expanding, so I want to use an Arrayformula to handle it. ...
0
votes
0answers
10 views

How can I calculate the remaining loan amount at a given year in a single formula?

If I have a loan like the one in the "Amortization Schedule" here where the following is the load terms: interest_rate 4.5% NPER 24 PV 50000 ...
1
vote
0answers
18 views

Google sheets increases the height of a cell after return key is pressed to exit a cell

As the following image demonstrates, pressing the return key on a cell, after entering content, randomly increases the cell height. Sometimes this does occur (1 in 10ish) and sometimes it doesn't. ...
0
votes
0answers
20 views

Google Sheets: Filter Row if it does not contains HYPERLINK

I have a sheet which has a column where most cells contain a hyperlink, I am looking for a way to find cells which do not contain a hyperlink and delete those rows. Filter by condition -> Text ...
0
votes
0answers
14 views

Multiple formulas (?) in one cell

I'm putting together a Google Sheet for my daughter's Girl Scout cookie sales. I have a cell for "Proceeds per box". If she sells 250 or less, her proceeds are $0.75 and for 251+ the proceeds are $0....
0
votes
0answers
19 views

Change the colour of a cell based on if the checkbox below an identically named cell on another sheet is true (Google Sheets)

This is something I've been struggling with for a little bit and don't know if its even possible. I would expect this to take likely hundreds of separate condition formatting rules but wanted to see ...
0
votes
1answer
22 views

Why is this custom formula conditional formatting rule triggering wrongly?

In this (partially redacted) sheet I want to set rules based on the Q column: Q is date-formatted but clearly is not blank, so why is the formatting being applied? I've tried other rules like exact ...
0
votes
0answers
19 views

Combined Columns into single

Very new to Google Sheets and i was wondering whether its possible to achieve this. I have a data set displayed as such: Want it presented in the following format: Any help on this would me much ...
0
votes
0answers
23 views

Copy sheet to new spreadsheet folder in selected folder

I know that I'm asking a simple question, but surprisingly, I haven't been able to find where my exact question has been asked before. Here's the situation: (This is method 1) I've got a sheet within ...
1
vote
0answers
19 views

How can I add a thick borderline after a certain text in Google Sheets?

I'm trying to modify my Working Hours Spreadsheet; What I want to do is add a thick borderline after each Sunday, so I can better identify each individual Week of the Month. Here's the Masterfile ...
0
votes
0answers
16 views

Google Sheets Work Schedule

On a protected sheet can permission be given to individual cells across ranges, for example allow Sheet1!A3;Sheet1!D3;Sheet1!G3 etc.? to be edited by John and Sheet1!B3;Sheet1!E3;Sheet1!H3 to be ...
1
vote
1answer
13 views

Google Sheets - Include a Comparison Trend Line

I am using google sheets to record sales data and have a separate worksheet for each calendar year. For each calendar year I include a couple of charts which illustrate things like track dollar value ...
1
vote
1answer
36 views

How to populate column with every unique instance in another column

I have a bunch of data on users and the user's name will be in Column A. The user's will always be unique, but they may show up more than once. I want to have another 2 columns (say E and F) that ...
0
votes
0answers
14 views

Auto-populating time instead of the Date, with the exact script below

The script below works perfectly for auto-populating the date. I need it to do the exact same thing but for time. I can't for the life of me figure it out. It does need to populate +1 to the right of ...
0
votes
0answers
31 views

Calculate value for first day of month from values for arbitrary dates

I have a series of energy meter readings in a sheet, taken at various dates over the last year. So some months have multiple readings, others only 1 or 2. Simplified example (most of my readings are ...
0
votes
1answer
28 views

Google Sheets onEdit uppercase script

I'm collecting one-letter responses from users in a Google Sheet and would like their responses to be made uppercase as they are input, but only within the cells D5-J[infinity]. I found a few ...
0
votes
0answers
12 views

How can I color parts of a chart differently in google sheets

I have a chart on google sheets, and I want to label certain heights of the chart by changing the background color. For example, anything above 50 the background should be yellow, and anything below ...
0
votes
0answers
15 views

On edit script for timestamp on multiple columns with individual timestamp based on the time of the edit made vice all having the same timestamp

I have check boxes every other column starting on column C, and I want the date stamp to appear in column d row 2. The script works great for that, but I also need it be for column e (check box) ...
-1
votes
0answers
18 views

Why don't my changes require verification for a google cloud project?

I am trying to verify a google sheets app so a client from a different organisation could use our previously internal tool. The app doesn't use any sensitive scoped google APIs; it just makes some ...
0
votes
0answers
11 views

Non-Linear Forecasting models in google sheets

There is a function in sheets to do 'FORECAST' but it's only linear however is does update as data changes. The only way to find a model with a good R^2 is to create a table and copy and paste the ...
0
votes
0answers
10 views

Using a different sheet for Moving Average in a line graph

I have daily values separated into sheets, one for each year. I am creating a line graph for each year and also adding a moving average with a period of 15 days, however this moving average starts 15 ...
0
votes
0answers
13 views

Check a cell if it includes any value from a range

In a Google Spreadsheet, I want to get a title of a range if a specific cell includes any value from the range. I have a range with names of transport like Bus, Tram, Boat and so forth. The range has ...
0
votes
0answers
13 views

Is it possible to add error bound range into Google sheets?

I just learnt how to make chart in Google sheets by this tutorial: https://business.tutsplus.com/tutorials/how-to-make-professional-charts-in-google-sheets--cms-28453 And my data is: Month ...
0
votes
0answers
21 views

Making a script to ignore strike through cells from DA onward when using the transpose/sort function

I've created a master list of sorts. I have several columns of information from DA onward that transpose and sort in column C. I'd like to be able to strike through any of these cells and have the ...
1
vote
1answer
41 views

Google Sheets IF function

I want to make a function with the following criteria: if A2 is equal to A1 -> Text 1 if A2 is empty -> Text 2 if A2 is not empty and not equal to A1 -> Text 3 Something like connecting these two ...
0
votes
1answer
17 views

Covariance matrix in Google Sheets for stocks

I'm trying to use a template to see what it says about my stock portfolio. it uses a covariance matrix with the data from excess returns. The template instructions say to use an add-on to calculate ...
0
votes
0answers
8 views

Google Sheets 2 way Sync [duplicate]

I need some help setting up two-way sync on my Google Sheets spreadsheet. I have created a MASTER tab and then filtered that into 4 different Manager Tabs and another filter based on the Period the ...
0
votes
0answers
26 views

Add Data Validation to cell B if cell A has content?

I'd like a dropdown to appear in each additional cell of a row if the first cell in the row has content. Is this possible? -- edited per request -- If I enter the word "Cats" to cell A1, I'd like a ...
1
vote
1answer
13 views

Format cell if today is 2 weeks after date in another cell

Basically trying to do the following with conditional formatting Format A2 IF TODAY is >= Date in A1+14. So if the date in A1 was two weeks ago or more, I need A2 to switch to a fill color.
0
votes
1answer
30 views

Google Sheets Conditional Formatting Cycle

In Google Sheets I would like to color a cell depending on if the cycle is on or off. The cycle is for 10 day the cycle is ON then for 5 days the cycle is OFF then repeat. So if I start the cycle on ...
2
votes
1answer
25 views

QUERY for two columns

I am trying to use one query to search in two nonconsecutive columns. So far I have the below formula. =QUERY(combine!A1:D,"SELECT * WHERE LOWER(A) LIKE LOWER("""&B1&""") or SELECT * WHERE ...
2
votes
1answer
25 views

If cell contains this text return that cell value

Look at the example I have below. A-.375-VG-M761 A-.375-VG-O630 A-.375-VG-O780 A-.75-VG-602 A-.75-VG-603 A-.75-VG-604 In one column I have two "SKUs": A-.375-VG and A-.75-VG. ...
0
votes
0answers
12 views

Problem 'Select an active sheet first'

There is a problem and i cannot solve it. 'Please select an active sheet first. (line 5, file "Copy of Code")" function onEdit(event){ //wa13669601 var ss = SpreadsheetApp.getActiveSpreadsheet()...
1
vote
1answer
14 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?
1
vote
1answer
14 views

How can I paste HYPERLINK formula in Google Sheets cell, to make it clickable link with defined text?

I got a script that takes some data and prepares it to paste into Google Sheets cells, some of which are supposed to be links with custom text. But when I paste hyperlink formula the script created, ...
1
vote
0answers
27 views

Filtering results on a lot of parameters

I'm searching for a way to filter +- 100 weighted averages, for which I've already made the correct formula. The datasets are in 7 different tabs and contain +- 40 columns and on average +- 50 rows. ...
1
vote
0answers
17 views

Count occurrences across sheets, based on criteria

Is there an elegant—more programatic way—to count across sheets based on some criteria like whether they have been checked. For example, given the attached sheets, the result would be: Apples: 1 ...
1
vote
0answers
16 views

Summarize all data from other tabs with same format and column

I have customer sheets which i must summarize into a single tab so i use this query; =QUERY({'ROC155'!B2:AB;'ROC152'!B2:AB;'ROC135'!B2:AB;'ROC002'!B2:AB;'ROC009'!B2:AB;'ROC145'!B2:AB;'ROC048'!B2:AB;'...