14,611 questions
-3
votes
1
answer
47
views
Change value depending on other field [closed]
I need to change the value of the dropbox in column J4 depending on the value in I4.
If I4 contains a value, then J4 should display a message like:
"Value needed, have a freaking cold winter&...
- 1
1
vote
1
answer
72
views
Calculations from Timestamp based on Checkbox Iterative now() in Google Sheets
I don't want to use Google Apps Scripts, just formulas so that this can be display/edited on a mobile device while offline.
[
- 25
Advice
0
votes
3
replies
71
views
How to keep a new column in sync with a computed column
Suppose I have two sheets: Sheet A's rows come from somewhere else, e.g. a Google Form, continually added to and kept in chronological order of addition, but subject to modification (e.g. manually). ...
- 445
0
votes
2
answers
119
views
LAMBDA( value; value) isn't a identity function [closed]
The formula
=SPLIT("1\10/10\00002.2";"\")
doesn't result equal to
=MAP(SPLIT("1\10/10\00002.2";"\"); LAMBDA(v; v))
At the former, implicity conversion is ...
- 384
Best practices
0
votes
7
replies
113
views
SPLIT without implicit conversion
I am experiencing problems with this section of my formula
=LET(
args; MAP(SPLIT("11/11"&CHAR(10)&CHAR(10)&"1"
; CHAR(10)&CHAR(10)); LAMBDA(v; TO_TEXT(v)))...
- 384
0
votes
1
answer
41
views
How do you fetch all the needed data in ALL the links in a column?
I can't really figure out how fetch and combine all the data in ALL THE LINKS in one Google Sheet.
This is the location of the the links:
SECTION LINKS'!B2:B
And this is what I want to get in every ...
-2
votes
2
answers
79
views
how do I tell which external Google Sheets a cell is pulling data from?
In a google sheet cell, I have the following formula:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xMGMl4GFs_2ZDxCrtfRMjG6CPMCdosiaVeCr47YYfb4","Monthly payments!B3")
it's ...
- 97
0
votes
1
answer
81
views
Filter in Filter or Query/array for multiple sheets
I'm a long time reader, first time poster.
I'm having a problem showing invoice details for each company id for this sheets.
https://docs.google.com/spreadsheets/d/1iTJ8PR3snsbZKh01-...
Advice
0
votes
3
replies
61
views
How to convert a column of duration text strings to numbers in minutes
I have a column (of known length) of duration text strings that contains potentially strings that are not duration. And I want to convert just the duration text strings to numbers in minutes.
For ...
- 10.1k
Best practices
0
votes
4
replies
73
views
Handling Multiple Sheet Inputs
I have a spreadsheet that receives data inputs externally. That data is used by sheet formulas to calculate an output which is then collected via an HTTP request.
What is the best way to allow google ...
0
votes
0
answers
83
views
Shift rows up/down in line with a dynamic list
I'm trying to get the data in rows to move up or down based on if col A grows or shrinks. However col A's data is defined by the range I want to shift. I haven't been able to find something that looks ...
- 9
Best practices
0
votes
4
replies
55
views
Google sheet IMPORTRANGE
I would like to receive your help.
I have 2 google sheets.
Sheet 1
Column A: all the rows contains codes
Column C: somethings in first 2 rows, but this is variable
Sheet 2
Column E: I would like to ...
1
vote
1
answer
83
views
How to extract multiple matched columns
I can extract matched columns using the FILTER function in Google Sheets, but I need help making it work when there are multiple matches. For example, if the header row contains the same value in ...
- 611
-3
votes
2
answers
196
views
How can I stack each 3 sets of 4 rows? [closed]
I have a report that vertically stacks yearly data made of two columns:
The first column shows the year and each of its 4 quarters (format: "YYYY Qx").
The second column shows a $ amount.
...
- 47
-3
votes
2
answers
118
views
Get Previous Month Date - Google Sheets
Using the formula =EDATE(A2, -1) to subtract one month from a date that falls on the 30th, when it reaches February, which has only 28 days, the formula returns the 28th. For the following months, it ...
0
votes
1
answer
104
views
Google Sheets Formulas
I was wondering if there is any way (formulas) that can change a cell's color when we are approaching the date previously entered in a cell. What I am looking for is a way to notify myself of upcoming ...
- 1
0
votes
2
answers
139
views
How can I count the occurrence of a text in other dataset?
I have 2 tables. This is the table that will be shown as a report:
File
Delay
1076759
1066123
1081690
1038312
This second table is the data source (different spreadsheet):
Entry Date
File
Status
09/28/...
0
votes
2
answers
130
views
Combining ARRAYFORMULA with QUERY on a filtered dataset when a specific condition is met [closed]
I'm trying to retrieve and transform data from employee overtime claims, as shown in the table below:
Original Table:
ID
NAME
DUPLICATE
IS MET
A01
Tom
NO
FALSE
A01
Meg
NO
TRUE
A01
Meg
YES
FALSE
A02
Al
...
- 13
0
votes
4
answers
233
views
Determine the first line of each occurrence of unsorted customers
I have a table with something like this:
1 Customer 1
2 Customer 1
3 Customer 1
4 Customer 2
5 Customer 3
6 Customer 1
7 Customer 1
8 Customer 3
9 Customer 3
10 Customer 4
My goal is to determine the ...
- 143
1
vote
1
answer
73
views
Google Sheets QUERY with IMPORTRANGE: contains fails on alphanumeric IDs
I’m combining multiple sheets with IMPORTRANGE and then querying them. The issue is with Col3, which contains IDs like:
ABC10000000001
ABC10000000001
ABC10000000001
or
52685485
59521458
55152655
=...
- 611
0
votes
1
answer
96
views
Checking multiple cells on a row and calculating a value based on cell conditions
I have the following table:
Ac.
Start Time
Ac.
Start Time
Ac.
Start Time
Ac.
Start Time
A
10:30
O
11:15
P
13:15
P
11:00
R
12:00
P
13:26
When it's entered, the activity ("Ac.") code ...
- 181
-2
votes
2
answers
148
views
Split N people in X constrained groups with Google Sheets
[EDIT]
Hi everyone,
I am looking for clues to work out a sorting formula with Sheets.
We are planning "teambuilding" school trips at our international college. I need to split a list of 200+ ...
- 9
3
votes
3
answers
86
views
Referencing a cell range
I have a Google Spreadsheet and a calculation as thus, which is repeated multiple times in various places:
=SUMIF(B2:B76, H3, E2:E76)
For ease of updating the range, I want to store the actual text ...
- 171
-2
votes
1
answer
85
views
Could I use a dynamic function to match values to another sheet in Google Sheets? [closed]
I'm making a quiz-type thing with Google Sheets- one as the user-end (where the quiztaker would input answers) and one as the answersheet. What I want to do is have a function in conditional ...
- 3
2
votes
3
answers
169
views
How to extract multiple blocks of rows between two keywords in a Google Sheets column into adjacent columns (formula-only)
I have a single column that contains mixed data (strings, numbers, sometimes blanks) and from time to time, there are marker rows that delimit blocks of data.
What I would like to do is:
Start ...
- 57
1
vote
4
answers
246
views
VLOOKUP with a WHERE condition on separate column
MY DATA
I have the following sheet of data, data:
PERSON
TEAM
Lookup
COUNT
FOO
A
Y
3
BAR
A
N
1
BAZ
A
N
4
FOO
B
N
1
BAR
B
N
2
BAZ
B
Y
5
FOO
C
N
3
BAR
C
Y
1
BAZ
C
N
2
MY INTENDED FINAL RESULT
I want to ...
- 1,645
0
votes
2
answers
85
views
Return the most recent entry of each course
I have a list of students at a campus and each of the courses they're currently taking with progress points for the date the data was pulled. Data will be entered weekly to see progress.
On the ...
- 17
0
votes
1
answer
47
views
How to use avg on datediff in a single Google Sheets Query?
In a Google Sheets Sheet I have a table with two dates in Column E and F.
I now want to get the average difference between the last 20 dates.
The Query I want to use is the following:
Select avg(...
- 1
-4
votes
2
answers
131
views
Why does this function work in Excel and not Google Sheets? [closed]
We have saved a file from a One Drive to our Google Drive and this formula will not compute any more. Can any suggest the fix please?
=IF(G85='September 25'!$D$1,(FILTER('September 25'!$A$2:$A$52,'...
1
vote
1
answer
133
views
How can I drag and count data from a different spreadsheet without helper columns or sheets?
I've been trying to solve this issue for a few days now, but no matter what I try I can't seem to get it to work the way I want it to.
Firstly, to set a few parameters that I've set myself since ...
- 47
0
votes
3
answers
127
views
Incrementing in formulas 1/n, 2/n, ... n/n
Let's determine to explain the problem that I have products that do not fit into one box. I need to mark every box. If the product fits up to 1 box, then in column C I only have the name of the ...
- 41
1
vote
2
answers
133
views
Convert Dates into 3‑Letter Month for Rows, Year for Columns, and Sum Sales
I have a dataset in Google Sheets like this:
Purchase Date
Price
9/24/2023
$49.00
10/25/2023
$29.00
1/4/2024
$29.00
1/8/2024
$39.00
1/31/2024
$39.00
2/21/2024
$29.00
3/13/2024
$49.00
3/13/2024
$39.00
...
- 611
2
votes
2
answers
107
views
Query fails with SELECT A, SUM(B) + SUM(C) GROUP BY A
Given a Google Sheet with the following data:
A
B
C
Jack
150
Jill
10
33
Jill
205
If I enter this function:
=query(A1:C3, "select A, sum(B) + sum(C) group by A", 0)
I get the following in ...
- 43
-3
votes
1
answer
103
views
identify duplicates across multiple columns with completeness rules [closed]
I have been analyzing this data over the past few months and have observed that identifying duplicates is quite time-intensive. The duplicates are determined based on five columns, including separate ...
- 611
0
votes
3
answers
129
views
Database Rows to static columns [closed]
I have a single column DB file that is rigidly formatted. It doesn't have a header or field names in the export that I've imported into a Goggle Sheet. I need help with how to approach this task of ...
- 379
0
votes
2
answers
123
views
Arrayformula with countif for column and check single cell
I need to make an arrayformula for checking a time input based on google form submission.
for example:
Mon Tue Wed Thu Fri Sat
17:00 08:30 17:00 08:30 08:30 17:00
...
- 11
-4
votes
1
answer
144
views
Calculate total profits or losses adjusting for future value each year
I am looking to create a spreadsheet that does the following: I give it initial revenue, initial fixed expenses, initial growing expenses, growth rate for revenue and growth expenses, number of years, ...
0
votes
2
answers
104
views
Formula to Display Each Multi-Select Value in Google Sheets Dropdown on a Separate Line
I having some difficulty solving the issue below. Would appreciate some help with this.
My Google Spreadsheet has two tabs. Product_Database and Data_Sample. In Data_Sample, I have a column of ...
- 1,070
1
vote
2
answers
130
views
Google sheets setting columns range in app script
I have this worksheet that im working on:
https://docs.google.com/spreadsheets/d/1u_CafEOKNLtthpWaXVo37BKiuRHQ2oGEC4liqkTmXuY/edit?usp=drivesdk
and I have this script:
function createWhatsAppHyperlink(...
- 11
-4
votes
2
answers
126
views
In sheet one, pull sheet two's cell values by using sheet one's cell values as variables?
In column B of Sheet One, I want display the value column A of Sheet Two based on the values of column A of Sheet One.
Below is the example of Sheet Two, which only has column A:
A
1 Orange
2 ...
- 2,457
0
votes
4
answers
145
views
Is there a formula to expand data to multiple rows per entry?
Here is my example table:
ID
Enroll Year
#Years
23001
2022
1
23006
2022
1
23015
2022
1
23014
2021
2
23004
2020
3
I would like to use the "#years" column to define how many rows should exist ...
0
votes
0
answers
63
views
How can I exclude a specific value from a MODE function?
I want to revise a formula that determines the contributor with the most contributions, changing or replacing it so that it excludes me from the list.
On the sheet MainData!, I have Column N for ...
- 45
1
vote
3
answers
137
views
Add empty row in an array, each time the content of a given column is different
I try to find a one-formula approach to be able to automatically add an empty row each time the content from one given column changes.
Ideally a one-line formula; I have seen some approached using ...
- 47
-2
votes
1
answer
105
views
Reduce number of characters in stacked Google spreadsheet query functions
For the following function, is there a way to iterate through the sheet IDs without having to literally write out each block again - the stack is growing; the only difference is the next sheet ID?
...
2
votes
2
answers
127
views
In Sheets returing a value from different tables based on multiple criteria
I have my data displayed on multiple tables in 6+ different sheets. This is how it's imported from a source.
What I need to do is find the value in different tables that correspond to the selections ...
- 23
-2
votes
1
answer
64
views
Closing balance per currency from a transaction log? [closed]
I'm trying to get an accurate maximum balance for an FBAR based on a Revolut transaction log spreadsheet.
The problem I've run into is that on a given day, I may have a series of transactions similar ...
0
votes
2
answers
199
views
How do I use INDIRECT to refer to combine arrays on different sheets?
Using Google Sheets, I have a series of tables on different sheets, and an Overview sheet that gathers all the totals.
I have a list of all the sheets in one column on the Overview page, and refer to ...
- 31
0
votes
1
answer
140
views
NEW QUESTION: How do I use SUMIFS with IMPORT RANGE in google sheets to sum a column based on referenced criteria (ex: "<="$K$5)?
UPDATED EDITS BELOW
I have a list of downloaded transactions that each correspond to a specific account code? I want to sum the amount (column G) based on the following criteria: segment (column A), ...
0
votes
3
answers
107
views
If option selected, count data from previous column
Basically if I have:
D
E
2
Yes
8
No
10
Yes
Would like to know the formula to count the number in Column D only if option 'YES' on dropdown menu on Column E is marked.
- 1
-2
votes
1
answer
97
views
vlookup to find employees of all clients [closed]
I have a spreadsheet with user details and all their clients in another column. I'm trying to form a new table with users in one column and all their clients in one row.
Actual table
User
Client
UserA
...
- 153