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") )
so workbook 1 is where the formula would go and the cells its refrencing would be "linelist" workbook.
the formula you provided I have already in the workbook when you click on the cell
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
- thank you! let me try your formula. I really appreciate all the assistance.