45 questions
0
votes
1
answer
44
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
63
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
...
- 344
1
vote
2
answers
98
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
124
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
59
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
92
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
372
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
339
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
618
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 ...
- 11.9k
1
vote
1
answer
258
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
702
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
77
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
128
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
134
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 ...
