Cross Sheet Formula help

I have a sheet that has Employee information including a location code and job title. I have another sheet where I want to use a cross sheet formula to get the employees name when matched with the location code and a job title. So in other words i need a formula that will look at the location code on my sheet and using that location code I want to search the other sheet for the word "manager" and the location code from the first sheet and then that would bring in the employees name. 

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Christopher Pretty

    See below general formula setup. You would need to create the cross sheet references (Bolded areas below).

    Replace "Area Manager" with another job title you are looking to check up (in your case depending on your column, you would replace it accordingly).

    =INDEX(COLLECT({Employee Info - Employee Name},{Employee Info - Location Code},[Location Code]@row, {Employee Info - Job Title},"Area Manager"),1)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!