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.
8,629
questions
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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", ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 11.2k
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 43.4k
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
...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 33
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 ...
CommunityBot
- 1
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 ...
- 11.2k
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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.
=...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 263
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 2,499
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 ...
- 2,499
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)
...
CommunityBot
- 1
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:...
CommunityBot
- 1
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
...
CommunityBot
- 1
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 "...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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. ...
- 11.2k
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 ...
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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%...
CommunityBot
- 1
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....
- 21
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 ...
CommunityBot
- 1