As of May 31, 2023, we have updated our Code of Conduct.

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 data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
3 votes
1 answer
59 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
1 answer
44 views

Sorting columns within a row, alphabetically

I have let's say I have rows in the following order: SN name1 grade1 name2 grade2 name3 grade3 1 Alice 97 Bob 90 Carl 80 2 Bob 80 Alice 100 Carl 95 3 Alice 100 Carl 80 Bob 90 Is there a way to ...
1 vote
1 answer
45 views

how to i setup a submit button from sheet 1 to remove the protection on sheet 2 so that a new user and input data then it will lock back up again

I have 2 sheets (Sheet 1 & Sheet 2), Sheet 1 has the sheet protected besides a few fillable cells including a apps script button to log the entered data to sheet 2. Sheet 2 is protected and can ...
1 vote
1 answer
22 views

Why does google sheets have a pop up formula box does any one actually use this box is there a way to disable it, [closed]

The extra formula box that pops up on google sheets is easily one of the dumbest and most annoying features I have encountered while using google sheets and I am surprised nobody else seems to talk ...
0 votes
2 answers
234 views

Extracting 2nd occurrence of a number in brackets / Google Sheet/Excel

I currently have cell that has the following output from my Google Sheet: Share Price vs. Fair ValueBelow Fair Value: AAPL ($126.85) is trading above our estimate of fairvalue ($118.02)Significantly ...
0 votes
1 answer
99 views

1 plot with several lines with a common X axis but unique X and Y values... is this possible?

I am trying to create a graph which shows several smooth lines that are defined by an X and Y value. the problem is that they have to share the X-axis. Is there a way to efficiently add the other ...
1 vote
2 answers
388 views

Apply FILTER() to result of TRANSPOSE() with custom condition

I've got the following data in a sheet: ColA ColB ColC ColD ... [lots of additional columns] ItemA A B C ItemB D E ItemC F ItemD G H (table A) I ...
1 vote
1 answer
56 views

How can I highlight the source cells in a formula (e.g. =SHEETA!$E$568 )

I have a sheet that makes extensive use of cells from another sheet with formulas, some of which have errors. The formulas look like: =SHEETA!$E$568 Is there a way, or a script, that can parse the ...
0 votes
1 answer
27 views

Creating a formula / macro to compare two sets of data and if they match to change the colour of a cell

I am trying to work out a formula or macro to automate a task that is laborious. I have to compare two sets of data and if they match I change the colour of a cell to show the task has been completed ...
0 votes
1 answer
43 views

Multiple conditions in same cell google sheets

=IF(A1=5, "green apple","red apple") Add to this new condition: =IF(A2=5,"green apple","red apple") Basically if A1 and A2 = 5 then "green apple", ...
1 vote
1 answer
33 views

Auto-Populate document name into cell in Sheets

I'm looking to auto-populate the document's name into a single cell in sheets no matter if/when the doc name is changed. I can enter a formula from a script =DocName(), but it won't update when the ...
1 vote
1 answer
23 views

Google Sheets - conditionally format a cell based on whether the content of the cell appears in a column from a different sheet?

I have a sheet called Master that contains a column of names in cells A1-A60 (e.g. A1: Ann,A2: Bob,A3: Chris, A4: Donna, etc.). I have another sheet called Subsheet that contains names that may or ...
1 vote
1 answer
34 views

How to add data in a specific row after searching a column for a specific keyword?

So I'm trying to figure out how to have sheets look in the username column for a specific username and then find the sum of the numbers in the "kills" column that are in the same row as that ...
1 vote
2 answers
109 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
1 answer
17 views

Return ' MATCH ' if cells contain equal value

I have a database with different names for the same phone number. But some of the mobile numbers contains same names as listed below. So I have to Match mobile numbers having same names as shown in ...
0 votes
1 answer
18 views

cells auto-populating from code when sheet is not called

I have built a code to reset cells to a certain value when a button is pushed. It works perfectly in the sheet that I want it too, however, it is auto-resetting in two other sheets as well, even ...
1 vote
1 answer
30 views

Google Sheets - Index Match multiple criteria and dates in headers

I am trying an index formula so that I can retrieve the value of dataset based on the item and the date. Data source formatted as below, with price valid until Start 01/Sept/2022 01/Oct /2022. 29/Oct ...
0 votes
0 answers
8 views

How to add days to a date based on values in 2 cells? [closed]

For example... I would like to add 30 days to the date listed in C2 based on the value in A2 and have it display in D2. I would like each "tier" to represent a different amount of days that ...
1 vote
1 answer
76 views

count the number of unique records within date range

I have a spreadsheet with the following three columns (A=resource code, B=start date, C=end date). I am trying to count the number of unique resource codes that overlap for any date of the year. A B ...
0 votes
1 answer
44 views

Query Function Not Putting Consolidated Data in the Right Place

So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
1 vote
1 answer
35 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 ...
0 votes
0 answers
10 views

Google sheets - Copy rows and insert in another tab with fórumla and automated process [closed]

I searched the forum and did not find an answer to my question. I need to automate a process and I don't know how, could you help me? Image take the line: (C2:H2) add all cells with +1 until cell F2 ...
0 votes
1 answer
24 views

Multiple IF Functions with Tick Boxes and Dates in Google Sheets

I have 5 columns, first two have dates, the other three are tick boxes. I would like to create a formula, where if: C2 box is checked (true), Google Sheets will add 1 day to the date written in A2 ...
2 votes
1 answer
34 views

Sum of Cells (supplementary question)

This question which specifically referred to summing a column of numbers until a break and then subtotalling on the next following line was answered very helpfully by this answer. Supplementary to ...
0 votes
1 answer
400 views

In Google Sheets, how would I go about finding column header of a filter result from another tab?

I have a simple form in one tab with a couple entries. On another tab there is a filter that automatically populates array based on form tab. Is there a simple way to reference a column header of a ...
1 vote
1 answer
26 views

how to sum to specific items combined

looking for some assitance, how can i get the sum of specific multiple items combined for example, i have a cleaning business, if on column a i have A1- WEEKLY A2-MONTH A3-On Call A4-Bi WK clients ...
1 vote
2 answers
63 views

Google sheets random number generation help

I'm trying to generate random numbers in an interval, x number of times. Imagine rpg dice sintax, like: 3d6 means roll 3 times a dice of 6 sides. Or 5d100 means roll 5 dices of 100 sides. If I was to ...
0 votes
1 answer
187 views

Create events from a spreadsheet and write relevant ids back to the spreadsheet

I have a function to create events for field based workers. Eventually I will add this to an onEdit trigger and give the user (centrally based admin worker) some functionality to edit/delete events ...
0 votes
1 answer
142 views

Can you filter a column in size order of an imported HTML? I have tried using query with no success

Using the below importhtml I'm hoping to order the numerical data of the third column in size order, but it seems I have lost my way on the model I was following. Any help greatly appreciated. =...
1 vote
1 answer
48 views

How do I return all results associated with an input cell in Google Sheets?

I have a time study data set that has three different order types and resulting times to fulfill that order. An Order Type will be entered into C8. In cells C9:C, I want to create an auto-updating ...
2 votes
0 answers
12 views

Moving data generated by a Google form from the response tab to another tab

I'm trying to copy data from a Google Sheets tab linked to a Google form into a new tab. I used the function ='Form Responses 1'!B2, then copied the formula in the other cells of the column. But when ...
0 votes
1 answer
81 views

In Google Sheets, I'm having some difficulty getting two curves on the same axis

In Google Sheets, I'm having some difficulty getting two curves on the same axis. How do I get a set of axis with 2 sets of x,y values on it. I looked at 1 plot with several lines with a common X axis ...
1 vote
2 answers
39 views

Is there a way to remove entries based on condition in =filter?

I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed ...
4 votes
2 answers
1k views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...
3 votes
1 answer
40 views

Formula error - Column result based on multiple rows values

The known: | A | B| C | D | |--|--|--|--| | Tenant | Flat no. | Duration stayed | Energy consumed in flat | To find out: E Energy consumed per tenant Condition: I have to consider the unique value ...
3 votes
1 answer
38 views

Filter a list of lottery results for those matching at least 4 numbers

I have a list of 3,000 winning lottery numbers. Each winning lottery number is on its own row and is comprised of 6 individual numbers, each in their own column. The winning number's draw date is also ...
3 votes
1 answer
4k views

Simplifying Google Form Checkbox Responses into Itemized List

This is more of a Google Sheets question than a Forms question. I want to receive form responses from checkbox questions (which unfortunately cannot be separated into separate rows) and flip the data ...
1 vote
1 answer
106 views

How to return a Lambda Function through a Custom Function?

I know there are alternatives to this. You can simply pass the variables you want to pass to the custom function. But that's not what I want to happen. Something like: =CUSTOMFUNCTION(A2,A3)(A4,A5) ...
2 votes
1 answer
180 views

REGEXMATCH across multiple worksheets in Google Sheets

I'd like to pull the "Meals" data from across multiple worksheets. The following produces the #ERROR message instead. =sum(filter('jan20':'aug30'!E10:E108, regexmatch('jan20':'aug30'!I10:...
0 votes
1 answer
21 views

Adding rows in Google sheet to create a chart

I am trying to create a Pie chart using Google sheet data such as this: Vendor Amount                                                            Airbnb $500 Airbnb $200 Airbnb $100 McDonalds $ 15 ...
1 vote
1 answer
1k views

Making a field mandatory in Google Sheet

I want to make a particular field (to be entered in a whole column) "Mandatory". So that if the person leaves the field empty while entering data, a dialogue box should pop up and say "...
1 vote
1 answer
52 views

Google Sheets: Two Functions at Once? Countif and Sum Maybe?

I'm trying to see how many times a person inputs the same answer into a form, and then take the top three people to use that certain answer. For example, people enter the "B Option", and I ...
1 vote
1 answer
35 views

Sum of Cells in the blank cells in the next column. ( Formula or Appscript)

I want to sum the cells until I get a blank cell in the column B then add that sum in next column. In the below pic sum of C2:C4 should come in D5,sum of C6:C10 in D11 and so on. Database is so large. ...
0 votes
0 answers
12 views

google sheets recalculates up to hundreds of times after a single change [closed]

I made a fairly large simulation of a board game in google sheets and it seems to be continuously recalculating after even the smallest change. For a while, I've been able to rerun the simulation by ...
0 votes
2 answers
34 views

Allow user submitted data from a button to a worksheet to which they don't have edit permissions

I have one shared worksheet that has a button to submit data to a second worksheet. Users submitting data from the first worksheet do not have editing privileges on the second worksheet. I don't want ...
1 vote
1 answer
219 views

Blinking cells in Google Sheets

I am trying to get a cell to blink when a file is opened. I currently have the following code that I have found from here, but don't know how to modify it to blink when the file is open. Can anyone ...
1 vote
1 answer
33 views

Sheets - Determine Cell Number with Equation

I'm trying to reference a cell by the following equation: =A(51-(48-(COUNTIF(Info!A5:A52,"*")))) A51 is the last possible cell that information could be in (hence the 51). 48 is the total number of ...
0 votes
1 answer
43 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%...
11 votes
2 answers
18k views

How to remove insignificant decimal point in Google Spreadsheets?

I'm trying to format a number in Google Spreadsheets. I want it to show the thousands comma, then a period ., followed by mostly 3 digits, only when necessary. For example: 1,234.56789 => 1,234....
0 votes
1 answer
57 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 ...

1
2 3 4 5
173