Search for a Name Across Lists


We keep a "Master List" of client names by fiscal year, with each year having its own sheet. Employees need to be able to search across sheets of the current and previous fiscal year to see if a name already appears. I initially created a report to return the correct information, however, I quickly found out that only licensed users can edit the report criteria (i.e., the search criteria). Does anyone have any suggestions for how this can be accomplished with an organization where most of the employees are free collaborators?


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Tara Day Ulrich

    Hope you are fine, you can create a helper sheet with a dropdown menu contains the list of the client names and 2 column for current year and previous year, then use the following formula for each year to check how many time the client name appears in each year:

    for 2020 =COUNTIFS({Client Name - 2020}, [Clients List]@row)

    for 2021 =COUNTIFS({Client Name - 2021}, [Clients List]@row)

    and if you like for more proficiently details like listing all the details for each client you select i can do it for you. please Email me

    my Email:

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!