Array and Index Training Records to pull who needs to complete something within a date range

Ellyn
Ellyn ✭✭
edited 06/08/23 in Formulas and Functions

I manage training records and am creating a dashboard to manage who needs to complete a training.

I have a master sheet with the employee name, and a reference column to them completing the training and when it is due

I then have a calculation sheet to count how many people fall within that date range

=COUNTIFS({HiPot Renewal}, >[Expiration Range 1]@row, {HiPot Renewal}, <[Expiration Range 2]@row)

Then, I have a dashboard to display my counts


My question is, can I build a sheet that instead of just counting, will pull the names/emails of the employees whos training renewal date falls in the range of the upcoming dates?

Keeping in mind that multiple people will have a renewal date that's due within in the range.

Answers

  • Anson Cheung
    Anson Cheung ✭✭✭✭

    Hi Ellym, you may create a Report (more specifically, Row Report) to filter the Master sheet with the desired criteria, say the training renewal date falls in the range of the upcoming dates.

    The report can then be embedded in your dashboard if needed.

    --Anson

  • Ellyn
    Ellyn ✭✭

    Hi Anson,

    It almost answers my question. If a pull a report, I can manually put in the dates of the expiration range, but what I want it to do it either automatically change the dates to the current year or pull the date range from the calculation sheet because that is set to update itself.


    This current way means I need to go in an update the date range each year/quarter

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!