Stack Overflow
Stack Overflow

Questions tagged [spill-range]

A new feature being released in Excel that "spills" values into an excel sheet.

https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/

34 questions
10
votes
2 answers

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 conditional formatting) method to dynamically format the spilled…
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
6
votes
1 answer

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 that there is a column of positive integers heading south…
jdaw1
  • 225
  • 3
  • 11
6
votes
6 answers

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:A8: 12 20 14 13 12 13 26 11 To find the…
kale
  • 191
  • 2
  • 8
4
votes
0 answers

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 know that I don't need a UDF like this if I use the…
pdtcaskey
  • 242
  • 1
  • 9
3
votes
3 answers

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 I would deploy this, it will not be a range, but…
mark fitzpatrick
  • 3,162
  • 2
  • 11
  • 23
2
votes
0 answers

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 automatically adjust to the number of columns in…
VaughanD
  • 21
  • 2
2
votes
1 answer

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 sum or sumif to a spilling formula (sequence,…
JPC
  • 25
  • 3
2
votes
1 answer

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 I expect. This specific question caused me to…
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
2
votes
2 answers

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 "mentions" as rows in A4#. The formulas in M4# and…
dholt
  • 33
  • 7
1
vote
1 answer

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 clustered column chart. I can manually do a table and…
Jayce
  • 539
  • 6
  • 21
1
vote
1 answer

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 B column C row 1 pos item qty row…
P.b
  • 8,293
  • 2
  • 10
  • 25
1
vote
2 answers

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(A1#>1,B1#>1)". I would expect a result of 20 rows with…
J Vegetable
  • 11
  • 2
0
votes
0 answers

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 dynamic so that the data which was initially on a10…
Poudyal Abishek
  • 1
0
votes
1 answer

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 some module: Public Function TestFunction(n As…
ChE Junkie
  • 326
  • 2
  • 9
0
votes
0 answers

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='Timeliness Issues'!J:J,1,"") T3 is one spill range and the J…
jillingworth
  • 3
  • 2
1
2 3