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
0
votes
0 answers

Using a filter with a spill range as criteria

I have a sheet using a spill range of product groups. However the groups are relatively detailed. IE: Pencils Black, Pencils Green, Pens Black, Pens Blue. There are 57 different detailed groups. I have a table that lists these items and concise…
Steve S
  • 1
  • 2
0
votes
1 answer

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>90,NA(),RandNum)) There is nothing else on this…
Andy Robertson
  • 171
  • 2
  • 6
0
votes
1 answer

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 highlighted on the graph as a data callout. Biggest problem I'm having is…
Kai Sosa
  • 3
  • 2
0
votes
2 answers

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 are populated by a spill range from dynamic array formula in…
markkeith
  • 25
  • 6
0
votes
2 answers

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 several criteria (hence the array). The formula works…
AesusV
  • 39
  • 6
0
votes
2 answers

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 names and ranges all together based on the current…
Squick
  • 1
  • 3
0
votes
1 answer

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:B10 to be filled with "Yes". If more data is added to column…
Wisp
  • 198
  • 1
  • 11
0
votes
1 answer

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 named range on the worksheet using "=ListCurJobs" returns the spilled range…
Munki Fisht
  • 37
  • 1
  • 6
0
votes
0 answers

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 the corresponding value under the column [Base] if…
Excellent
  • 3
  • 4
0
votes
0 answers

Count the number of unique numbers/strings in a column, but limit the set to the rows that satisfy a certain condition in another column

This: "'=COUNTA(UNIQUE(D2:D724))"' works, but now I want to limit the input to this formula as follows: only for the rows for which in another column a logical condition is satisfied, count the number of unique numbers/strings in column D for this…
babipsylon
  • 325
  • 2
  • 12
0
votes
1 answer

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 A1:F9) and sort it: NAME CARD NUMBER ACCOUNT…
P.b
  • 8,293
  • 2
  • 10
  • 25
0
votes
1 answer

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 sales, with the following data : date, customer,…
laurent
  • 5
  • 1
  • 3
0
votes
1 answer

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, with the following data : date, customer,…
laurent
  • 5
  • 1
  • 3
0
votes
2 answers

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 "spilled" over to subsequent rows and only 1…
Saaru Lindestøkke
  • 2,067
  • 1
  • 25
  • 51
0
votes
1 answer

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 files with macros that are adapted after the…
user2693457
  • 1
  • 2
1
2
3