= LET(
m, ROWS(active),
n, COLUMNS(active),
k, SEQUENCE(m*n),
r, 1+QUOTIENT(k-1,n),
c, 1+MOD(k-1,n),
x, IF(INDEX(active,r,c)=@Cust,INDEX(InvNo,r),""),
y, IF(INDEX(active,r,c)=@Cust,INDEX(Dates,c),0),
d, IF({1,0},x,y),
FILTER(d, y>0) )
= LAMBDA(cst,
LET(
m, ROWS(active),
n, COLUMNS(active),
k, SEQUENCE(m*n),
r, 1+QUOTIENT(k-1,n),
c, 1+MOD(k-1,n),
x, IF(INDEX(active,r,c)=cst,INDEX(InvNo,r),""),
y, IF(INDEX(active,r,c)=cst,INDEX(Dates,c),0),
d, IF({1,0},x,y),
FILTER(d, y>0) )
)(@Cust)
= DETAILS(@Cust)
= LET(
n, COUNTIFS(active, @Cust),
k, SEQUENCE(n),
filtered, IF(active=@Cust, coordinates, ""),
v, SMALL(filtered,k),
IF({1,0}, INDEX(InvNo, QUOTIENT(v,1000000)), MOD(v,1000000)))
A formula that could be ported back to traditional Excel might be
Code:= LET( n, COUNTIFS(active, @Cust), k, SEQUENCE(n), filtered, IF(active=@Cust, coordinates, ""), v, SMALL(filtered,k), IF({1,0}, INDEX(InvNo, QUOTIENT(v,1000000)), MOD(v,1000000)))
The second sheet picks up the customer name from the list in Column A, @Cust meaning the particular value on the same row as the formula.
The first sheet uses a dropdown called 'selection' which seems to be surviving your updating process better.
The sheet 2 formulas are to be preferred because the calculation is more visible, rather than being tucked away in Name manager.
I have edited references to @Cust to change them to 'selection' which should help.
The Lambda's offer the neatest and most flexible approaches, but are still on beta release.
From your description, the problems may be due to our using different versions of Excel or at least different update states. Nearly all my Excel formulas start
= LET(
so, if that is not available to you, nothing will refresh correctly. I have taken the sheet 1 formula that you seem to be happiest with and moved it to sheet 2. That particular version of the formula depends heavily on formulas written within defined names so they are central to any debugging and validation exercises.
View attachment 74118