Forum Discussion

Brass Contributor

Jul 14, 2023

Solved

Formula assistance

Hello, I was wondering if there was formula I could use that would essentially list out any person where the calREDIE DI# column is blank (Column Au would blank), and fill each row (Photo below tha...

Workbook New.xlsm772 KB

Formulas and Functions

flexyourdata

Jul 18, 2023

jaolvera

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")
)

Riny_van_Eekelen

Platinum Contributor

Jul 17, 2023

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")
    )
)

jaolvera

Brass Contributor

Jul 18, 2023

thank you! let me try your formula. I really appreciate all the assistance.