Forum Discussion
Solved
Formula Help
Hello Everyone, I have attached a sample of my workbook and here is what I am trying to accomplish. I have a Tab called ALL! (yes I know, will change it some day) that houses names and information ...
In sheet Lists in the attached file you can enter your priorities as shown in range C4:C20. The formula in the attached file displays the data ordered according to the values of range C4:C20.
How about this:
=LET(
headers, {"BLDG","WING","ROOM","NAME","GNDR","MAIN A/C","WINDOW A/C","PORTABLE","OCC STATUS"},
bldg, ALL!A3:A170,
wing, ALL!B3:B170,
room, ALL!C3:C170,
name, ALL!D3:D170,
gndr, ALL!H3:H170,
mainAC, ALL!N3:N170,
windowAC, ALL!O3:O170,
portable, ALL!P3:P170,
occStatus, ALL!T3:T170,
data, CHOOSE({1,2,3,4,5,6,7,8,9}, bldg, wing, room, name, gndr, mainAC, windowAC, portable, occStatus),
blankRow, HSTACK("", "", "", "", "", "", "", "", ""),
blankBlock, VSTACK(blankRow, blankRow),
sectioned, REDUCE(VSTACK(headers), StatusList,
LAMBDA(acc, s,
LET(
labelRow, HSTACK("Occ Status: " & s, "", "", "", "", "", "", "", ""),
filtered, FILTER(data, occStatus = s, SEQUENCE(0)),
IF(ROWS(filtered) > 0,
VSTACK(acc, labelRow, filtered, blankBlock),
acc
)
)
)
),
sectioned
)
For some reason, this formula results in a #CALC! error.
