Forum Discussion
Formula assistance
Yes, edit the second argument to FILTER:
=LET( missing, FILTER(Linelist!B2:D4, (Linelist!AU2:AU4 = "")*(LineList!B2:B4<>"")), TAKE(missing, , 1) & ", " & CHOOSECOLS(missing, 2) & " " & TEXT(TAKE(missing, , -1), "mm/dd/yyyy") )
jaolvera I misread your question and now see that you want info on the missing IDs. My initial formula took ID's that were not missing. Then you should also check that e.g. the Last Name is not empty, otherwise the formula will result in a spill error. And I indeed forgot to properly format the Birth Date in the TEXTJOIN function. Inspired by flexyourdata , change the formula in S12 as mentioned below and it shall work in your Dashboard.
=LET(
missing, FILTER(
Linelist!B:D,
(Linelist!B:B <> "") *
(Linelist!AU:AU = "")
),
TEXTJOIN(
", ",
,
DROP(missing, , -1),
TEXT(TAKE(missing, , -1), "mm/dd/yyyy")
)
)
Riny_van_Eekelenhello! the formula worked! but is there anyway for the data to be filled in each line versus all in one cell?
I.e each person who has a missing ID will be listed in each cell
jaolvera Please upload a file with some fake data. Perhaps I just misunderstood or couldn't force what you are dealing with.
I have uploaded the spread sheet, thank you
You had the spill error from my original formula because your input area for the formula is comprised of merged cells. You should un-merge the white cells for columns R and S, then enter the formula:
=LET( missing, FILTER(Linelist!B2:D4, Linelist!AU2:AU4 = ""), TAKE(missing, , 1) & ", " & CHOOSECOLS(missing, 2) & " " & TEXT(TAKE(missing, , -1), "mm/dd/yyyy") )
The issue is that TEXTJOIN doesn't join row-wise. So, you either need to wrap it in BYROW and use TEXTJOIN row-wise or revert to the simple column concatenation as in my original formula.