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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
30 views

I want date with yyyy/mm/dd format on Google Sheets

I want to convert mm/dd/yyyy to yyyy/mm/dd with function on Google Sheets. I applied TEXT function like the picture but it's not working. How can I convert this?
user avatar
0 votes
0 answers
30 views

How to pull data from different tab with dropdown?

Very inexperienced Sheets user here. I want to pull data from one tab and present it on another in relation to a dropdown. Demo Sheet: https://docs.google.com/spreadsheets/d/...
user avatar
  • 9
0 votes
2 answers
33 views

Google Sheets - Function that generates range reference based on 'base' and 'offset' values

I'm looking for a function whose interface is the same as the OFFSET function but returns the range reference (i.e. A2:A20) instead of the actual range itself. Below, I've outlined this imaginary ...
user avatar
  • 3
0 votes
1 answer
20 views

How to find duplicates within each multiline cell against a range of multiline and single line cells?

I am a novice with Google sheet, please pardon my ignorance. I inherited a data sheet that I am trying to improve. It's a massive data that continues to grow each day. The daily task: I receive a list ...
user avatar
-1 votes
0 answers
15 views

Google Sheets: How to copy documents and retain access from IMPORTRANGE on copies? [duplicate]

I do not believe this question has been answered elsewhere. I am creating Google Sheets Document A and linking it via IMPORTRANGE back to Google Sheets Document B which contains the data. Both of ...
user avatar
0 votes
0 answers
58 views

How to do multivariate regression of data in array in Google Sheets

Background What I want to achieve I have some handy lookup tables that I want to find a formula for, so that I can plug in arbitrary numbers between the ones the table lists, and get serviceable ...
user avatar
  • 101
0 votes
1 answer
30 views

Why do my data points end up in the wrong places in my graph?

So I am trying to insert a vertical line in my graph in Google Sheets, by following the solution given here: https://stackoverflow.com/questions/58026200/add-a-vertical-line-to-google-sheet-line-chart ...
user avatar
0 votes
0 answers
28 views

Populate Google Sheet with Data From People Chip

I would like to create a google sheet that places different information contained in a given contact card in to separate columns with the maximum amount of automation possible. Is this possible, and ...
user avatar
0 votes
1 answer
21 views

"Multiply" (Concatenate) Text Columns in Google Sheets

Simple question that I can't seem to find a solution for. I have text data in two columns like Name Num a 1 b 2 and I would like to produce a single formula if possible to output a 3rd column ...
user avatar
  • 1
1 vote
2 answers
22 views

If a cell has a value, display text?

I'm trying to display text in a cell if another cell has a value. I can do this successfully, however it adds an extra space that I can't have One column has a unit number, the next column has an ...
user avatar
  • 11
0 votes
1 answer
25 views

Google Sheets - countif with dynamic range

In sheet1 have a column with a list of IDs, each with a set of statuses: ID Apr May Jun 1 Load Load Complete 2 Load Complete NA 3 Load Load Load In a separate sheet, sheet2 I ...
user avatar
  • 101
0 votes
1 answer
19 views

Sending Data Across Duplicated Sheets in Google Sheets

I am wondering how to "send" data from one cell so that it automatically adds its contents to a cell, in another sheet, which contains ongoing totals. In Sheet 1, I have a cell called TOTAL ...
user avatar
  • 1
0 votes
1 answer
21 views

Unsort Google Spreadsheet column

I have sorted a column from A to Z to examine what I wanted. And now I want to bring it back to its initial state. How can I do that?
user avatar
  • 123
0 votes
1 answer
23 views

IF formula for Multiple Rows

I'm trying to create an if formula to look at an entire column and if it matches a number of rows then add the value_if_true. So I want to look at the entire column C to see if there is a match to ...
user avatar
  • 1
0 votes
1 answer
19 views

Can I pull data from a Google Sheet into inline text in a Google Doc?

I am creating exams for my students in Google Docs and would like to easily change the values and answer options without having to do all the math each time. I would like to have a google sheet where ...
user avatar
  • 1
0 votes
0 answers
19 views

I am having trouble having an updated daily GOOGLEFINANCE output

I'd wish to have an updated 1-week price change of the current stock, but I can't set the date parameter to a value and set it a week back, and I can't use an updating date from another cell. I just ...
user avatar
  • 1
0 votes
0 answers
13 views

"OutputSheet": function on edit: lookup value in "LookUpSheet" and change OutputSheet.value

I have a sheet with a column that contains a dropdown. The options in that dropdown are readible strings (idiot proof) But for further processing, these strings need to be converted to numbers. So on ...
user avatar
0 votes
1 answer
19 views

How can i make a formula to determine payment types

I am having trouble nesting and using multiple criteria. I want a column to determine if payment type was CASH, CHECK or WAITING depending on if the cells for Check# and Payment are containing data. ...
user avatar
1 vote
2 answers
33 views

Using Query to select colum based on headers

I have a sheet with a large table of data with a header for each column. I want to import a specific column to another sheet using the header name. I am using the below formula to do so (which has ...
user avatar
  • 11
0 votes
1 answer
18 views

Possible to make a Google sheet macro available to all sheets I access?

I have created a very basic google sheet macro that reformats columns by automatically adjusting their widths, and then widening the column(s) by a certain amount, to avoid crowding columns next to ...
user avatar
0 votes
0 answers
13 views

How to trace cells from a formula when the formula uses cells form a different sheet?

I have a complicated budget spreadsheet which I update regularly. I have one sheet which is essentially just my bank ledger where I categorize all transactions & purchases. And then I have another ...
user avatar
  • 1
0 votes
1 answer
34 views

Filtering multiple columns

I'm trying to filter multiple columns using this formula: =OR(A:A="Text", B:B="Text") https://www.statology.org/google-sheets-filter-multiple-columns/#:~:text=Step%202%3A%20Apply%...
user avatar
  • 1
0 votes
1 answer
36 views

Help with SumIfs - Trying to create a sickness tracker that can track how many days of company sick pay someone has left, in a rolling year

I'm trying to create a spreadsheet than can track the amount of sick pay people have left in a rolling year, I'm having difficulty with with getting it to track properly however. I have two sheets, a ...
user avatar
  • 1
0 votes
1 answer
26 views

How do I find the appearance rate of one list of cells in another list of cells?

I imported all of the data from my iTunes library into a Google sheet for my own amusement, and I want to know by album how many songs from each album I thought were good enough to put in my "...
user avatar
-2 votes
1 answer
31 views

Help on making Google Sheets script quicker and more efficient

I have the following script, and I would like to know if there is anyway to make it more efficient? It works great, but takes a little time to do what it needs to do. Mainly is there a way to condense ...
user avatar
1 vote
1 answer
31 views

How to stack query results into single cell formula

I'd like to generate a URL based on a list of items that will change over time and I'm having a little trouble. My data looks like this: Items URL Item 1 Item1URL Item 2 Item2URL Item 3 Item3URL ...
user avatar
  • 11
0 votes
2 answers
41 views

Vlookup with column search by headers

I am downloading the "Data" report from the system, in which the order of the columns may be different, but their headers will always be the same and the first "ID" column will ...
user avatar
0 votes
1 answer
27 views

How to use a formula in an axis title?

I would like to write the number of values into the legend. How can I use a formula in a custom label? Example: I would like to add the number of days to the legend of the trendline.
user avatar
  • 145
0 votes
1 answer
50 views

getRange() Error Exception: The starting row of the range is too small

I have this little script that's giving me some trouble. I get the following error: Error Exception: The starting row of the range is too small appendToRecords @ Code.gs:10 What I'm trying to ...
user avatar
  • 3
0 votes
2 answers
28 views

Google sheets: =SUM shows incorrect fraction

When doing a simple sum of cells in google sheets, the result is being reported as a fraction somehow? And the values make no sense? Does anyone know why this is happening? In the pic below, you can ...
user avatar
0 votes
1 answer
17 views

I can not make a query work, I need another eyes to look it

I have 3 columns and what I need to do is a query where I can see Lab name, Sum Job count only N, Sum redos only Y. I tried with array, but not let me get the conditional Col2=N, Please help me. =...
user avatar
  • 1
0 votes
1 answer
26 views

IF AND Formula Google Sheets Help

I'm trying to write a formula that can match 2 criteria and if the match is TRUE then it returns the average of the range provided. Criteria are: User ID Week number. So for the user id provided, I ...
user avatar
0 votes
1 answer
12 views

Value 0 when IFS are not filled

I want to give the "draw" value 0 when the score cell is not filled - possible? Example below https://docs.google.com/spreadsheets/d/1vFSoq1m6VHFGkHVCGtNfObbyGTbQKNLPRegnFsBrOK0/edit#gid=0
user avatar
0 votes
1 answer
20 views

Tournament table Google Sheets

I want to make a tournament table and I need to: make A1 cell type amount of points based on score (B1 is player 1 score, C1 is player 2 score) I want it to automatically detect wins, draws and loses ...
user avatar
0 votes
1 answer
24 views

Conditional formatting - color cell if less than cell to the left - (inverted yield curve)

Example In the following, K2 is less than J2, so it's highlighted in red: Conditional rule This was easy to achieve with the following conditional rule: Question What's a good way to get that rule ...
user avatar
2 votes
1 answer
32 views

Google Sheets script add formula into cell but cell value doesn't update

I have a script that when I run it, should add a formula info cells, if the cells are empty, the script works without any problems but if I try to run it again to update the values of the formula, the ...
user avatar
0 votes
1 answer
19 views

Color Cell by using the note in Google Sheets

Whenever I try to use the following script... function onEdit(e){ if(e.range.getNote()){ e.range.setBackground('green'); } } I get the following error... TypeError: Cannot read property '...
user avatar
0 votes
1 answer
14 views

High and low score benchmarking query in Google Sheets

I am trying to query the highest and lowest scoring colour within a group. Then to query what the benchmark score is for those colours in all groups. I'm totally stuck on an approach. I have marked in ...
user avatar
0 votes
1 answer
15 views

How to write an IF formula for a multiple choices?

I have one drop down cell that contains choices of "A Company", "B Company". The next cell has a drop down with choices of "Founder", "Director", "Lead&...
user avatar
0 votes
1 answer
16 views

[SUM IF CELL ABOVE EQUALS CELL BELOW]

[SUM IF CELL ABOVE EQUALS CELL BELOW]. * link to the file https://docs.google.com/spreadsheets/d/1GFS6J1vX88vsOCsKzrMB7Zf82m9QOHqu6oAmO4eJwH8/edit#gid=0 * in column f of the file I'd like to get the ...
user avatar
0 votes
1 answer
17 views

What is wrong with the formular?

I am bloody new to this forum - so if I am doing something wrong, please let me know. Here is my question: I want to build a URL in Google Sheets where you have to fill out some parts (it for tracking)...
user avatar
  • 1
0 votes
1 answer
17 views

Dual Filter, trying to equal one of a range

In Google Sheets, I have the following filter working which gives me a column range of values: =FILTER(INPUT!$E$2:$E, INPUT!$A$2:$A=B2) And a second (temporarily seperate for testing) filter working ...
user avatar
  • 101
0 votes
1 answer
39 views

How can I show specific points in a smooth line curve on Google Sheets

I was able to create this graph However, I want to know how I can have dots that represent the specific points, while still having this be a smooth curve.
user avatar
0 votes
1 answer
31 views

IMPORTRANGE with condition

I Would need help with this one. I would like to transfer data from one Google workbook to another using importrange but with a condition. Condition: If column 2 is Apple (transfer data from column 3) ...
user avatar
  • 1
0 votes
1 answer
26 views

I'm trying to automate my swim team's event list

We used to do everything on paper, now I have a basic sheet that saves a lot of time but is still not very efficient and could be improved. The link is to an image of a simplified sheet. We input our ...
user avatar
0 votes
1 answer
26 views

How to add values in a cell with values?

I have a value in a cell, to which I want to add another value and display that new value. Example: In cell A1 I have "1000". In Cell A2 I have "100". I want A2 to add to A1. ...
user avatar
0 votes
1 answer
22 views

How do I refer to grouped rows in Google Sheets?

I would like the value in the cell immediately above cells in grouped rows below to reflect the max of the values in those grouped cells. Is there a way to refer to the adjacent grouped rows in this ...
user avatar
  • 550
0 votes
2 answers
28 views

How can I sort multiple ranges by one reference cell that is outside the second range?

I have a roster that has (on sheet 1 ('Roster')): Name, 1st Week of Attendance, 2nd Week of Attendance, and then other non-relevant data. On the second sheet ('Contact Info' it has Name, a cell that ...
user avatar
0 votes
1 answer
21 views

How to highlight duplicate values when clicking on a specific cell

I'm new to spreadsheets, but trying to put a functional one together for my team. I have a list of member names, and several lists of tasks they are assigned on different days of the week. The goal is ...
user avatar
0 votes
0 answers
41 views

Run a set of assumptions through a formula

I hope you can help! I am trying to forecast revenue for a business, and determining the revenue per client is quite complicated, and there are many factors at play. The simplest way I found to do it ...
user avatar