Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
376
questions
1
vote
1answer
14 views
Array formula for adding letters after duplicate numbers in order
The below formula adds a letter after a sequential number, A, B, C, D, etc for the same number until it finds a new number, then it starts again from A.
I need help on turning this into an array ...
1
vote
1answer
10 views
Escape single quote in cell content in a QUERY
My Problem
I have a Google Spreadsheet with two tabs - one containing regexes and values:
And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
0
votes
1answer
12 views
Assigning a value to a name from a list
I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
0
votes
1answer
12 views
Can I populate Google Sheets cells adjacent to a target cell?
I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
0
votes
1answer
14 views
Conditional formatting with multiple names in list
Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like:
H3 = John, Steve, Bob, Dan
Is there a way to use conditional ...
1
vote
1answer
10 views
How to create new cell from keyword of another cell?
I have a column that has job listings with extra info, so I only want to grab the job position and place it into a new cell.
A1: Seeking Assistant to the marketing division...
B1: Assistant
(...
1
vote
1answer
20 views
How to extract data from a table dynamically?
I have the following table
And I would like a formula that would produce the following result
I would like to iterate through the table going from left to right top to bottom and if there is a ...
0
votes
1answer
18 views
How to search value in another column?
Using Google Sheets, I am trying to two compare two columns on Google Sheets, like the one below:
Basically, I want to compare column A and B, basically, if a cell in column A is inside a cell in ...
1
vote
1answer
28 views
How to Iterate between colunns and rows?
I am running the following forumula:
=IF(INDEX(C$2:$2, 1, ROW(1:1)),CONCATENATE(B$2,"-",INDEX(C$1:$1, 1, ROW(1:1))))
On the following table
Basically, If a value is present in column C2, I am ...
0
votes
1answer
8 views
Google Sheets images
When I insert an image in a cell in Google Sheets from my camera how is that image stored in the cell? I know it creates an image in my phone in a folder called images, but if I want to link that ...
1
vote
1answer
21 views
Delete Google Sheet rows matching multiple criteria with custom formula
I have a Google sheet where I am trying to remove multiple rows matching any one of a vector of values using (Filter by condition -> Custom formula).
For this example, let's say I want to remove ...
1
vote
1answer
16 views
How can I return a string instead of a number when the number is 0
I have a formula that returns average lbs, waist and body fat % for the weeks I've been dieting. The problem is I didn't measure body fat % until a while after I started so some averages are 0. I'd ...
1
vote
1answer
23 views
How can I change the formula of one cell based on the value in a different cell?
COLUMN D: Dropdown list of values
COLUMN E: IF function returning different values depending on COLUMN D contents
I have the functionality described above working properly. My problem is that I want ...
1
vote
1answer
13 views
How to get an array for a range per a column in Google Spreadsheets?
I'm looking for creating a similar formula:
=UNIQUE({A:A;B:B;C:C})
So, I need such kind of a range {A2:A;B2:B;C2:C;...} (each column separated with the semicolon) for several hundred columns.
Seem, ...
2
votes
2answers
74 views
Google Sheets Split row in multiple rows
I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form.
I need a script that can arrange the data like in the blue rectangle.
1
vote
2answers
26 views
Google Sheets IF and 2 x ANDS
I have three columns in my google sheet.
COL A - will hold an invoice number (text field) or be empty
COL B - will display the word EXEPNSE
COL C - this will be my formula column
I need to detect if ...
0
votes
1answer
16 views
Import and SUM chosen (from drop-down) column to Sheet1 from Sheet2
What formula I can use in cell C2 to import one column from sheet 2 using a drop-down list?
Here's the document.
2
votes
1answer
14 views
Dynamically creating column names based on data in another tab
I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted.
The ...
1
vote
1answer
15 views
Pull image or skip to next cell if empty
I am currently using Google Sheets to make a list of videos and thumbnails.
I have various sheets titled: Frying, Stewing, Baking, etc. Within each sheet, there are only two columns which include a ...
0
votes
1answer
15 views
Pull data when cell value is selected
I am Trying to pull data from part of a sheet to another part depending on if dirt level 22 value is filled in. Please see attached Picture, basically, the bottom section pulls data from rows above ...
1
vote
1answer
17 views
Conversion from LibreOffice to Google Sheets
I use LibreOffice to create xlsx files. There are some formulas to test conditions. Everything is ok under LibreOffice but when I open the file with Google Sheets formulas give the wrong result.
e.g....
0
votes
1answer
8 views
How to check separate sheet cell for any data, and return a Y?
I'm using several Google sheets to capture product data and optimize it. One sheet will feed data into the next, and then again into another sheet, using this formula:
=IMPORTRANGE("https://url-to-...
1
vote
1answer
10 views
Output data if value is less than today's date
I have a spreadsheet. I want to create a formula that checks the date of one cell and if the value is lower than today's date, it to output the word required into another cell. How would I do this?
1
vote
1answer
24 views
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows
I've been trying to get this array formula to work, but I get an error. It's just a vlookup array to display multiple columns. It works fine without the {""; bit, but I want it to drop down one row.
=...
1
vote
1answer
16 views
Skip arrayformula if the result of that formula is blank
I have an array formula that looks like this:
=ArrayFormula(IF(COUNTIF(B2:B, QUERY('Form Responses'!B2:G, "select G where B != ''"))=0, IFERROR(QUERY('Form Responses'!B2:G, "select B, G where B != ''"...
0
votes
1answer
19 views
Replacing data in Google Sheets
I am new to coding! I have data in Sheet 1 of a Larger Spreadsheet that looks like this
25.06.18
and wish to change it to look like
26/06/18
I have tried to write a macro:
function Dated() {
...
1
vote
1answer
17 views
Outputing Array Formula in one row
I came across the below array formula, which works really well, I'm just struggling to tweak it, and barely understand it's inner workings, but it's very good. Instead of displaying a total of stock ...
1
vote
1answer
17 views
Solve errors in a series of FILTER functions within array brackets?
I have been working on a budget project on Google Sheets. My goal is for a range of data to be filtered by date (which is represented as an integer between 1 and 31 for the day of the month). All ...
0
votes
1answer
9 views
SUM weekly values according to date in one column A and values in column B
As shown in the screenshot, I have values I am looking to add for weekly totals to track, however, this is very tedious having to create a new code for every week, especially as the weeks change per ...
1
vote
1answer
28 views
How to programmatically drag new rows - running total?
I have the first few rows of my mortgage payments set up, and I want to know how to programmatically print the rest of the rows, as opposed to dragging the little corner box to output the rows.
This ...
1
vote
1answer
26 views
Array Formula with IF function and SUMIFS function
I am trying to convert the below formula into an array formula. I can make it work in an array, but it displays the wrong values, compared to the normal formula which I have to drag down.
Normal ...
0
votes
1answer
28 views
Dynamic Data Validation in Google Sheets
I want to restrict a value in a cell based on another cell in the same row.
The value in A must not exceed what is in B. If the value in B is zero, any figure is allowed in A
A | B
---
4 | 9 – ...
1
vote
1answer
13 views
Count unique values in second column corresponding to unique value in first column
I have a Google sheet with cities corresponding to countries like this. Notice that neither countries nor cities are unique and there can be duplicates.
| A | B |
----------------------...
1
vote
1answer
12 views
How to refer to elements of a dynamic array in Google Sheets?
I'm trying to create teaching timetables dynamically, by searching through a large table with all of the information about what time a lesson takes place, who the tutor is, what age group is being ...
-2
votes
1answer
23 views
How do I find latest invoice number & dates?
Trying to find a formula for the latest date and invoice number.
Here is my sheet:
https://docs.google.com/spreadsheets/d/1T_rFYDCQhqfrsU9claYPHQTrEz68zpUFKG9HZqqV4Z8/edit?usp=sharing
1
vote
1answer
14 views
ArrayFormula not showing 0s
I use the following formula to calculate plain text so to show leading zeros.
=ARRAYFORMULA(SUM(S2:S17+0))
but it's only displaying the results without the leading zero.
Example, if It is adding up ...
2
votes
1answer
23 views
How to aggregate data from multiple Google sheets with a tag
I am importing data (using IMPORTRANGE) from 3 different sheets to 3 different tabs of a google sheet.
Then I am aggregating those three sheets into one and also filter them using a query.
Ex:
=...
0
votes
1answer
18 views
Sort sentences based on formatting
I have a list of sentences in Google Sheets, each word in a different column.
There is a stupid man.
How we came here we don't know anything about.
Finally, it has come to this.
Shaking my head.
How ...
1
vote
1answer
11 views
Issue with finding the averages of multiple rows at once
I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
0
votes
2answers
9 views
Using IF functions with formatting in Google Sheets
I'm working on a Google Sheet and want to reformat all instances where the number in a cell is "2" and the color of the cell is pink. Is it possible to use information about cell formatting (here, e.g....
1
vote
1answer
57 views
How to generate all combinations of letters in a word?
Using Google Sheets, how would I show all possible ways to type a word, for example:
word
Word
WOrd
WORd
WORD
wOrd
woRd
worD
without any length word?
The given word would go on Row A and the ...
0
votes
1answer
11 views
Google Sheet Query returns first 3 rows in one row
I have made a simple query that combines data from 4 separate sheets:
=query({'1.MA'!A13:O200;'2.PF'!A13:O200;'3.BM'!A13:O200;'4.C'!A13:O200},"Select * where Col2 is not null ")
The objective is to ...
1
vote
1answer
29 views
Automatic merging of duplicate cells under common ID
I have data in column A with IDs and a column B with data for those IDs.
Right now I have multiple (usually 2-3) rows with the same ID but different data. What formula to use to merge those into one ...
1
vote
1answer
26 views
Google Finance Historical Data with Multiple Attributes?
=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-($N$2)),TODAY(),"DAILY")
The above code returns OHLC data for specified days with the date column.
What I want are only high, low and close columns.
=...
0
votes
1answer
33 views
Repeat formula n number of times in Google Sheets
Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
1
vote
1answer
15 views
How to get cell's parent ID?
Firstly thank you for helping me out. I'm in the construction industry and don't have any clue how to best do this - not my forte!
I'm trying to bulk add a bunch of building product categories to my ...
0
votes
1answer
29 views
Fetch value of a cell based on dropdown menu items results on another sheet
I have 2 Sheets named as Sheet1 and Sheet2...
Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column.
Sheet2: ...
1
vote
1answer
12 views
Adding mm:ss times when expressed as TEXT
I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets.
The numbers are supposed to be minute second. mm:ss. For the ...
0
votes
1answer
13 views
Merging data sets from two tables with origin reference [duplicate]
In a Google Sheet, I have multiple tables containing a list of dates.
I want to create a single (sorted) list containing all the dates and the table they came from.
Table foo:
1.1.19
3.1.19
Table ...
1
vote
1answer
42 views
Filter and display duplicates in a table
Here's the link to my Google sheet: https://docs.google.com/spreadsheets/d/1SBD0_Ca0r_p3810u1ZNlbG6MXsvhFlLcwEJndVQJRP0/edit?usp=sharing
The master data will show:
And I would like to have a table ...
