= 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)))
Thank you so much Peter, it seems that you have this working. I, however, am not much versed with these functions and as of now I am unable to use these solutions on my file. What is the range that is to be defined as 'Cust' and how do I use these formulas...through VBA or directly.. Could you please bear with me and let me know the process.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)))
Hi Peter,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.
Peter,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