Search for a Name Across Lists

Tara Day Ulrich

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

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: Bassam.k@mobilproject.it