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.
7,431
questions
0
votes
1answer
18 views
How to paste or import CSV containing newlines into Google Sheets
I'm trying to copy/paste or export a large number of lines from another application to Google Sheets. However, I discovered that when I copy/paste, any newlines are silently removed. When I try to ...
0
votes
1answer
7 views
Arrange comma delineated responses into a bulleted list
I used the following formula to help create my solution and used a tilda as I can't find the code for a bullet. My question is how do I add a tilda to the beginning of the first response?
This is the ...
0
votes
1answer
13 views
Google Sheets - searching combinations of cells for value maxes
I'm trying to do some organizing for my characters in a video game I play. I'm not sure if/how I can do what I'm trying to do. Essentially, I'd like the "Max Crew Skills" cell range in the ...
0
votes
1answer
9 views
Multiple Columns, Multiple Criteria Count
Sorry if I don't word this perfectly, but I'm trying to count multiple options in multiple columns. The current formula I've attempted is:
=COUNTIFS('Data Dump'!AA:AA,{"Awaiting Copy","...
0
votes
1answer
7 views
How do I best sum data in a table in google sheet based on a set of criteria?
I have a table with 14 columns (see attached image):
First column (column A) is the name of the expense
Column 2-13 (column B-M) is the months January to December (with January = 1, February = 2… ...
0
votes
1answer
19 views
Counting repetition with associated quantity
I would like to count the number of times my products are sold.
I tried COUNTIF, but it does not take into account the quantity associated obviously.
In the columns E G I I have the number of products ...
0
votes
0answers
9 views
Unable to use Google Docs URL invite action in new URL to invite user to a copy of a document
I have created a google sheets document that I am wanting to send out to a number of users for them to make copies of, share back with me and then edit themselves.
I am using the largely under ...
0
votes
1answer
8 views
How to convert "standard" dates (Gregorian calendar) to Ethiopian calendar?
Is there a way to convert normal dates in a Google Sheet to the Ethiopian calendar (13 month, around 7 years back) and vice versa via a formula? I believe it does not work with a simple formula, so is ...
1
vote
0answers
9 views
Google Sheets Zapier Parse Question
Zapier's parser is reading innaccurately despite confirming our template, so the pipeline (slightly Odd, I know - not my choice) is:
Google Voice > Gmail (filter > automatic label) > Zapier ...
0
votes
0answers
13 views
How can I create separate sheets with rows of specific values based on data from a source sheet? [closed]
I have a sheet with multiple columns. Column E has four possible values: '-', 'Home', 'Away', 'Office'.
I want to create three separate sheets from the first sheet. One sheet for all of the rows with '...
0
votes
0answers
14 views
Google Sheets Formula - Auto update row number for cells
I feel this should be easier to find but I'm really struggling. I want to populate a column with names from another tab (depending on the class chosen in A1). When I paste my formula into a cell in ...
0
votes
0answers
21 views
Using Custom Count and Sum and I want to only sum visible cells
I've been using the Custom Count & Sum add-on to allow me to sum cells of a particular background-color. This has worked fine so far, however, I would now like to be able to sum only visible cells....
0
votes
0answers
18 views
Copy and Paste Values Across GSheets
there's something that's been driving me nuts. I can't copy a cell from one Gsheet (cell contains a formula) and then copy and paste values to a DIFFERENT Gsheet-nothing happens!. It doesn't even work ...
0
votes
0answers
23 views
Google Sheets if a cell is green, get value [closed]
I want to search column C18:C30 (all cells have dates in them) for cells with Green background. There may be 1, 2 or 3 but always 1. Then insert the date of the 1st one in C13, if 2 insert the 2nd in ...
0
votes
1answer
28 views
Need help with Google Sheets!
0
I need help figuring out a formula. In this example: Column E is Start and F is Finish. I want to write a formula in column J that writes an "X" with the following conditions:
E > 7:00 ...
0
votes
1answer
16 views
Google Sheets Replace REGEXMATCH with REGEXEXTRACT
I am working with a Google Sheet where column A contains a set of values. Every value contains the following string "SAME_WORD LETTER", where LETTER is one of the alphabet letters, like &...
0
votes
0answers
14 views
How to add a part in this script to send a Google Sheets tab as Excel as attachment in the email? [closed]
function emailChart() {
var s = SpreadsheetApp.getActive().getSheetByName('Views for Kristel');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getActiveSheet().getDataRange();
...
0
votes
1answer
11 views
Google Sheets IFS Text result based on multiple conditions FIX & SORT
I'm very new to Google Sheets and I'm trying to make find the correct formula for a text result based on multiple conditions, here is my best crack at it:
=arrayformula(IFS(B9:B="",V9:V=&...
0
votes
0answers
12 views
Can I make a relational data validation with setDataValidation in google sheets?
Apologies, cannot share sheet bc company work.
I have two columns. The first is data validated against a static list, the second is data validated dependent on what was selected in the first column. ...
0
votes
1answer
13 views
Importing data from Investors.com, getting #N/A (google sheets)
I am attempting to pull from https://research.investors.com/stock-quotes/nasdaq-apple-inc-aapl.htm
I want to get the EPS Rating which is the 305th list item, which I found using
var i = 1; [].forEach....
0
votes
1answer
8 views
How to assign a name to a membership number vice versa?
I would like to learn what formula I can use to make my timesheet more efficient.
How to assign a membership number to a name vice versa? I need to add lesson hours to each membership daily and ...
0
votes
0answers
12 views
I don't want to "share" my Google Sheets. How do I delete all of the email addresses which are offered as choices to share with when I open Sheets?
When I open Google Sheets, a box opens up asking if I want to share the spreadsheet. Is it possible to remove the option to share Google Sheets?
0
votes
1answer
9 views
Change value of a cell based on a drop menu of values on a different sheet for invoice
I have seen this question in similar posts, but I can't get it to work. When I select the product in the drop down in column B, I want it to populate the unit price in column F. If no selection in ...
0
votes
1answer
13 views
Drag IMPORTRANGE formula and auto fill multiple cells
I import multiple cells by a checkbox with this formula:
=IF(A1=True;ARRAYFORMULA(IMPORTRANGE("URL";"Sheet1!A1:A10"));"")
But I'm looking for a solution to be able to ...
0
votes
1answer
17 views
How can I find the number of word matches within a cell?
I have a cell (B) that contains the following URL:
https://www.solicitors.co.uk/what-is-on-police-bail-but-not-charged/
I have another cell (A) a that contains a search query:
police bail without ...
1
vote
1answer
19 views
Using multiple IF statements to return a value based on multiple ranges
I'm trying to write an IF statement for whether an engine size is small (900 - 1400), medium (1401 - 2000) or large (>2000).
The engine sizes are in the B column.
I'm not really sure how to ...
0
votes
1answer
9 views
Google sheets import columns based on date value
Google sheets for simplicity's sake say I have two tabs, one with master data and one that is a search function to display a certain part of the master data. The issue is I only want import certain ...
0
votes
1answer
13 views
How can I convert $14,500.00 from text to a number in Google Sheets?
As the title says: How can I convert data like $14,500.00 and $1,206.80 from text to a number in Google Sheets?
I tried the special format and then $#,##0.00but it didnt work. Any suggestions?
0
votes
1answer
21 views
How do I conduct conditional data validation multiple times so that each input prompts a different conditional validation outcome
I am a member of a project that does electricity monitoring, and I am trying to organize our contact sheet to indicate the status of our electricity monitoring across locations. I want to make the ...
0
votes
1answer
11 views
How to convert a range of cells values to a row of values in google sheets?
In google sheets, I have a range of cells, say A1:D6, I am to using importrange function to grab this range to another sheet.
My requirement is to populate this data into one row A1:A24 (24 elements)....
2
votes
1answer
35 views
I wish to make my Google sheet slightly easier for me to use
Can anyone teach me how to autofill the "Selling At" column E and "SC Used Per Transaction" when I key in the "Denomination Sold" Column?
So for example, when I key in &...
0
votes
2answers
47 views
Which of multiple checkboxes is singly checked? I need it's cell reference
I am seeking a worksheet formula to ideally return the cell column letter (or at least the cell reference) of the checkbox that is checked from a range of checkboxes.
Given this range of checkboxes(F1:...
0
votes
0answers
56 views
How to reverse the y-axis in google sheets when the y-axis is time of day?
So I'm trying to make a Marey graph, and I have it mostly figured out, but I can't figure out how to get it so that 12 AM is followed by 1 AM and so on, currently it starts at 12 AM, then goes to 11 ...
0
votes
1answer
21 views
Google Sheets, Referring to a cell range
Here's what I am trying to do.
In cell C15, I put the cell reference of another cell, e.g. B2.
I another cell, cell C16, I want to check that IF the value in cell C15 contains the cell reference to ...
1
vote
1answer
22 views
Math operations inside looped formula
I'm looking how to to do a math operations for specific row depend on the row the switch formula append.
The challenge
I have a table with product, their product price and a shipment
price.
The ...
0
votes
1answer
13 views
Get value of merged cells for query function
I'm trying to find if someone has been scheduled in the AM and PM in a table like this:
So I want to write something like this:
=QUERY(A13:B1002,"select A,B where A matches 'AM' and B contains '...
0
votes
2answers
32 views
"Marking" or "tagging" noncontiguous cells for later use in a formula
Is there is a way to 'mark' a cell (or a set of cells) throughout a worksheet or workbook in Google Sheets, such that I can perform operations on these easily later.
The real world application of this ...
0
votes
1answer
14 views
How to use a dynamic value from a cell as row number for a cell reference?
I do believe, that there must be a very simple solution to my problem - but I am just too stupid to know it or to find it.
I am in sheet 2 of google sheets table and I do want to use a simple ...
0
votes
1answer
26 views
Using importxml to parse data from investing.com
I am trying to use the importxml to get the percentage changes data from https://www.investing.com/crypto/bitcoin.
Unfortunately, when I retrieve it with the following formula, it retrieves other ...
0
votes
1answer
12 views
When I try to paste 8739297430300090 in a cell, it defaults to 8.7393E+15 on google Excel. I have done nothing different than previous cells
I am working on a spreadsheet for a business. I have been copying and pasting phone numbers, names, emails, and their ID numbers. This particular number, 8739297430300090 is defaulting to 8.7393E+15. ...
0
votes
0answers
20 views
Search all tabs in entire spreadsheet (Google Sheets)
I am making a simple task manager for three users in Google Sheets.
The data is added in separate project sheets:
A summary page splits all data by user (from the "Who" column), and should ...
0
votes
1answer
14 views
How can I change the format to a popup calendar in Google Sheets?
I set the format in the cell to DD/MM/YY but when I click on that cell, I get a popup calendar with the week starting with Monday. But I want the calendar to start on Sunday. My Google Calendar is set ...
0
votes
1answer
37 views
How to insert first numeric value in a column?
I'm trying to create a formula which will in the absence of data for itself, reference a column for the first piece of numeric data. However, I'm not sure what formula I should be using. Below is a ...
0
votes
1answer
25 views
highlight cell in column B on every first not empty cell in following columns
I'm working on a medicine sheet, where I want to highlight the times when a pill is added to one of the following columns.
Right now it looks like this:
Each pill works for (about) 7 hours, so I take ...
0
votes
1answer
16 views
How to calculate total return in google sheets
We have a column of monthly returns (profit/loss in percent).
month1:%10
month2:%3
month3:%8
total return =(1.10*1.03*1.08)-1 =0.2236 (%22.36)
Is there a function to calculate this?
0
votes
1answer
17 views
Google Sheets sorting correctly then incorrectly after a few seconds
I'm working on the following spreadsheet that takes the results of a set of quizzes and then converts it to a league table.
https://docs.google.com/spreadsheets/d/...
0
votes
1answer
12 views
How to have a number deduct from another automatically each day
SO what im trying to do, is Have a balance automatically deduct a predefined number each day.
E.g -5000 each day. So first day it would be for example 20000, then once a day has elapsed, it deducts ...
0
votes
1answer
32 views
Google Sheets: how to reference a range of cells for charts using a formula?
I'm trying to make a chart always take the range from the first 8 cells within a column without updating the range when a new row is added above the range. You could use something like "=INDIRECT(...
0
votes
0answers
15 views
Auto-Fill a column with a range formula, and have the reference cells increase in increments relative to the range size
I'm trying to auto-fill a column with a formula =SUM(SHEET1!B5:B11) but it goes up in increments of 1 not 7, so the next row down says =SUM(SHEET1!B6:B12), whereas I want =SUM(SHEET1!B12:B18). (7 ...
0
votes
1answer
16 views
How to make an output of a column that "filters" through another?
Essentially, I have a Column A with a bunch of websites, and Column B with a bunch of acceptable websites. I'd like column C to display the websites from Column A, except those that contain url's from ...
