48 questions
3
votes
3
answers
93
views
Excel array formula using if() with two rows from spilled array source
I have set of data that I am pulling from another sheet with filter() function, and then I'd like to use array formula if possible (with # operator) to have formula spill dynamically for as much rows ...
- 215
0
votes
3
answers
159
views
Workaround for #spill! in Excel when creating random integer array of random size (with constraints)?
How to construct a random size, random integer array with (exact) target sum value with a single Excel function (or 'best' viable/working solution given the 'known' volatile issue with this sort of ...
- 2,644
0
votes
1
answer
80
views
Why Excel Formula returns an array?
I have a problem with the last SWITCH in this formula. If this condition ISERR(Articles) is met, it returns one single celle with the string, which is the expected behavior. But if the COUNT(...) ...
- 354
0
votes
1
answer
91
views
Writing Spill/Array Data to Excel using OpenXML in C# causes cell to become uneditable
Using OpenXML to write "spill" or array data to Excel. This is data that all lives in one cell, but spills over into other cells.
If the cell already has a spill/array in it and then I run ...
- 83
0
votes
1
answer
84
views
How to stack columns of differents length in Excel [duplicate]
In have a woorkbook with many sheets. All sheets have the same format of content.
I want to stack cells from one specific column of many sheets.
I give in cell A1 the list of the sheets : data;Blad7
...
- 354
1
vote
2
answers
106
views
Excel spillrange with sum
SUM(FILTER(Tbl_Assets[WDV on Year 0],(Tbl_Assets[Asset Group as per Co. Act (SCH II)]=Q14)*((Tbl_Assets[Disposal Date]>StartDate)+(Tbl_Assets[Disposal Date]=0)),0))
In the above formula, if I try ...
-1
votes
1
answer
176
views
referencing spilled ranges as the table array in lookup... but not spilling
in row 4 from D4 onwards i have a transposed spilled range of stock tickers (UNIQUE from a table reference). Each has their stock price history on a sheet of its own, the matching sheet names are ...
- 13
0
votes
1
answer
69
views
What is Excel formula to show Top N by category?
I have a Spill Array. How can I take only top 1 for each product. Basically I want to filter to only values highlighted in yellow.
Formula for Spill Array
=GROUPBY(HSTACK(SL_2025[Product],SL_2025[...
- 1
2
votes
0
answers
94
views
Excel not returning 2d spill from XLOOKUP [duplicate]
I think I can do this with words.
I have the following:-
=XLOOKUP($B6,$W6#,$AA6#:$AD6#,"")
B6 = A key
W6# = A dynamic array of keys
AA6#:AD6# = 4 pieces of data related to key.
Works just ...
2
votes
4
answers
456
views
Making LET spill
I have the current formula
=LET(
rad, ROW(C2),
sumArea, SUM(INDIRECT(ADDRESS(rad,3)):INDIRECT(ADDRESS(rad+5,3))),
result, sumArea/INDIRECT(ADDRESS(rad+6,2)),
result
)
which calculates ...
- 185
0
votes
0
answers
368
views
XLOOKUP unexpected behavior with spill range
I have a table that looks something like this
ID
Values 1
Values 2
Values 3
1
123
456
789
2
234
567
890
3
345
678
901
4
678
901
234
I'm using ID value to locate the row of interest (e.g. row ID=3) via ...
- 55
0
votes
0
answers
677
views
TOCOL function does not give expected results
I was working on an answer to the following question: https://stackoverflow.com/a/77547442/12634230 when I got a formula that returned the correct results, but when any value was changed within the ...
- 12k
1
vote
1
answer
275
views
Dynamically spilling a function in Excel - matching value on array outside the lambda [duplicate]
I know how to create formulas in Excel that select values on a join key (example in image below).
However, I would like to be able to create one formula that replicates this function for the whole ...
0
votes
1
answer
821
views
SumIfs only returning first value (not summing all values that meet criteria)
Hi I have the following formula that I'm trying to debug:
=SUMIFS(INDEX(Sheet6!$B$3#,,2),INDEX(Sheet6!$B$3#,,3),[@Date])
The formula behaves more like a lookup, returning only the first value it ...
- 1
0
votes
0
answers
84
views
Insight on speeding up array formulas (SCAN)
I have a formula system to predict values based on a mathematical model and then calculate a square sum of the error with empirical data. I'm using the solver to minimize the error but the whole ...
0
votes
0
answers
144
views
How can I write formulas with conditions and spill in Excel (IF plus FILTER)?
I’m trying to use the FILTER formula in Excel to return all values from a column that meet three possible conditions:
If column D equals "CDI" or equals "SELIC", return the value ...
- 169
2
votes
0
answers
138
views
Calculate a diagonal total using dynamic arrays on Excel
I need to calculate a diagonal total of numbers in a dynamic array on an Excel sheet. The number of columns will increase but there will always be seven rows in the tableau, so I need the formula to ...
- 21
0
votes
0
answers
104
views
Is there any VBA code for automatic Row addition or deletion in excel,,, whenever unique value changes and there is data below that formulae?
suppose i have 10 unique data in some column i recently have data in a10, now when i enter the unique formula in cell a2 it will show spill error as i already have data in a10, i want to make that ...
0
votes
1
answer
2k
views
How to programmatically get the values of a spilled Excel range in VBA?
Range value properties all return EMPTY in executing code.
How can I programmatically get the values of a spilled range?
I'm using the following code as a test function, you can copy and paste into ...
- 326
0
votes
1
answer
540
views
In Excel, I have two spill ranges, how do I return TRUE if a value occurs in both?
In an excel workbook, I have two spill ranges. I want a third column that will return "1" if the same value occurs in both spill ranges. Normally, I would write a formula like
=IF(T3='...
1
vote
1
answer
193
views
How can I get my Excel spill range to be grouped by a specific column for a chart?
I need to produce a chart in Excel to show a quantity of items for a number of categories, grouped by the month they were received. At the moment, every occurrence of the Month shows up in my ...
0
votes
1
answer
911
views
Excel #SPILL! error when there is nothing else in the spill range
In cell A1 i have the following:
=TOCOL(E1:G6,3)
In Cell B1 i have the following
=UNIQUE(VSTACK(E1:E6,F1:F6,G1:G6))
In cells E1 to G6 I have this formula
=LET(RandNum,RANDBETWEEN(1,100),IF(RandNum>...
- 826
0
votes
1
answer
116
views
Excel: Attempting to have 2 tables of different size communicate without a #SPILL error
I run into the error because I am trying to make a string of formulas that read when between 2 dates in "table 1" is negative it pulls information from "table 2" to have it ...
- 3
1
vote
1
answer
185
views
Excel DROP function unexpected behaviour
When getting a formula solution for this question Insert row to separates group of data with header I tried dropping the first and last row from a spill result.
This was the data used:
column A
column ...
- 12k
0
votes
2
answers
1k
views
VBA to insert or delete rows in named range based on spill range
Cross posted at: https://www.mrexcel.com/board/threads/vba-to-insert-or-delete-rows-in-named-range-based-on-spill-range.1214814/#post-5937281
I have a named range "nameList" (B3:E20) that ...
- 25
0
votes
2
answers
3k
views
Excel, How to display results of an array formula/values from a range of cells in a single cell
Preface: I'm using Excel 2016**
I've got an array formula that is searching through a data set to ultimately find a list (with a dynamic length, hence the offset formula) of values based off of ...
- 49
0
votes
2
answers
640
views
Update multiple Named Ranges created by Dynamic Source using Spill
In Excel 365, currently I manually update multiple named ranges based on a dynamic header row, with dynamic values under each header. I hoping someone can help me with using VBA code to update the ...
- 1
0
votes
1
answer
308
views
Is there a way to spill fill a column based on count of nonblank cells in adjacent column in Excel?
I am looking to find a way to fill a whole column with the same output, "Yes", based on the number of cells in the adjacent column.
For example, if there's data in A2:A10, I would like B2:...
- 308
0
votes
1
answer
593
views
Trouble passing spilled range to VBA - Only getting Application-defined or object-defined error
I'm having an issue passing a Spill array to VBA. The range is defined by as name "ListCurJobs" and the equation
=UNIQUE(FILTER(CtrlSht!$B:$B,CtrlSht!$B:$B<>""))
Testing the ...
- 47
0
votes
0
answers
552
views
How to use Spilled range as criteria in COUNTIFS funtion in excel?
I am trying to use a spilled range as criteria in the COUNTIFS function but the result I am getting is a blank spilled range and it's not working the way I want it to.
So I want the code to spit out ...
2
votes
1
answer
4k
views
Can one do a sumif(s) on a dynamic (spilled) range and return a (2d) array?
Just curious if it's possible to get spill range output from a sum or sumif formula in excel. Given a 2D array, I'd like to sum some rows but preserve the columns. My concern is that once you add a ...
- 25
2
votes
3
answers
976
views
Converting Multidimensional Arrays Into A Spill Range
I've been posting a lot of Array/Spill Range answers lately trying to generate non-vba/App Script solutions. However, I've run into situations where I have arrays within arrays that aren't spilling as ...
- 10.1k
0
votes
1
answer
6k
views
Excel sort function to "skip" 1st row
My question is because I wanted to sort the data as provided in the following question:
How to select all the column based minimum date value in a sheet
What I wanted to do is select the data (Range ...
- 12k
2
votes
2
answers
2k
views
Row-wise textjoin of dynamic array with lookup
This question is closely related to this answer from user mark fitzpatrick.
My sheet image
I have a variable list of country code headers in M3# and AB3# (same list) and a variable list of unique &...
- 63
1
vote
2
answers
685
views
Excel OR function doesn't allow spill range?
how do i get OR function to return a spill range?
For example, in excel 365, cell A1 and B1, I have a spill range of sequence(20). Which creates 2 sequence of 1-20. In c1, I tried to put in "=OR(...
- 11
4
votes
0
answers
743
views
Excel SpillParent and SpillingToRange Difficulty
I'm trying to use .SpillParent and .SpillingToRange on Range objects in Excel in some UDF's and get unexpected behaviors when the range is not on the same worksheet that the UDF is called from.
(I ...
- 242
3
votes
3
answers
2k
views
Excel Dynamic Array formula to create a running product of a column
I need to create a running product from a column of numbers (I could use a row, but a column is easier to demonstrate here.) The input might be any arbitrary array. In fact, in the application where ...
- 3,392
0
votes
1
answer
3k
views
Use TEXTJOIN with FILTER and reference to a spill range
I am trying to query a list of sales stored in Excel by using TEXTJOIN and FILTER and referencing a spill range and this does not work.
Here is the context:
I have a table tabSales containing the ...
- 5
0
votes
1
answer
822
views
Call spill range within dynamic array formula
I am trying to query a list of sales stored in Excel using a dynamic array formula referencing a spill range and this does not work.
Here is the context:
I have a table tabSales containing the sales, ...
- 5
0
votes
2
answers
108
views
Why does the AND function collapse the result into a single value when using named ranges?
Question
I'm using named ranges extensively in my workbook, but now find that they are not a simple substitute for regular ranges in the case of the AND function. In that case the formula is no longer ...
- 2,605
0
votes
1
answer
623
views
Dynamic Arrays in Excel not Spilling
I just bought a new computer and installed Windows 10 and latest version of office 365 Home.
When I use dynamic arrays in Excel there is suddenly no longer any spill which creates issues since I use ...
0
votes
1
answer
258
views
Using the LEFT function to extract everything before a number doesn't work well with spilled arrays
I am currently trying to extract the prefix of a store ID to be able to then generate a list of stores with only that prefix.
Cell D1 has that formula to extract the unique prefix :
=TRANSPOSE(UNIQUE(...
- 97
0
votes
2
answers
1k
views
How to Use Excel Spill Range To Convert Multi-Column Array into Two Column Array
I'm trying to create a spill-range solution to turn a list of dates and multiple columns of names into a structured two columns of data. I can do this using VBA, but because this will be automated and ...
- 10.1k
0
votes
1
answer
61
views
IF statement Not Recalculating Past Length of Referenced Spill Range in Excel
I have a column that is a spill range that uses the filter and sort_by formulas. This spill range is dynamic and I have an IF statement that references the spill range but upon recalculation of the ...
- 87
0
votes
1
answer
128
views
Excel: Spill out all matching rows
For an Excel documenten I am fitlering the data to create a "view". I got several rows of data containing the following data
| type | sender | duration | price |
In my view I want the ...
- 312
6
votes
1
answer
451
views
Spill ranges: casting arrays to intersection value with @
Before Excel introduced spill ranges, before the “@” operator, one could ‘cast’ a range into a single value with “0+” (numeric values) or “""&” (strings). But “@” isn’t quite the same.
Assume ...
- 251
7
votes
6
answers
14k
views
Cumulative Sum Formula using new Excel Dynamic Array Formulas
I'm using the new Dynamic (ie Spill) formulas in Excel. I want a dynamic array that is the accumulation or running total of another dynamic array.
Let's assume I have the following dynamic data in A1:...
- 201
10
votes
3
answers
7k
views
How to Dynamically Format An Excel Spill Range?
Microsoft is about to release a new "Spill" feature for Excel. At time of post, this not available in the current version, but insiders can use it.
Is there an "easy" (non-vba or ...
- 10.1k