creating formula

I have two different sheets. One is populated from when a person fills out a form and they insert the location plus the fiscla year of 2023 - 01 January.

Then there is another sheet where al the locations are listed and the columns names are 2023 - 01 January and so on.

how do i make it so that when the form filled out with the location and the fiscal year then match the other sheet and shows a yes that they reported for that fiscal year for that specific location.


  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    Could you provide a screen shot of what you're working on? It sounds like you're trying to match a value on a separate sheet with multiple criteria, which means I think you'd like to use the COLLECT() function.

    It'd be something like

    =INDEX(COLLECT({Cross Sheet Location Range},Location@row,{Cross Sheet Fiscal Year Range},[Fiscal Year]@row),1)

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!