How to automatically fill cells using criteria from another cell?

Aaron Kennedy

This might not be possible but I'll try to explain.

I have a sheet with a list of jobs, basically an exported list from another website, this list does not contain the locations of jobs, but another sheet does. Would it be possible to have the "location" column automatically fill with the correct location from the other sheet when the job code matches a job code from the other sheet.

This probably doesn't work but please let me know if it's possible.

KPH

If I have understood correctly, then my answer is "yes" this is possible. If…

Sheet 1

• Contains a Column of Jobs
• Contains Column of Job Codes
• Needs Column of Job Locations to be automatically populated

Sheet 2

• Contains Column of Job Codes
• Contains Column of Job Locations

You can add a formula to Sheet 1 that can look up the location in sheet 2 based on the job code that appears in both sheets.

You can do this using VLOOKUP or INDEX and MATCH. The latter is my preference. And will want to build this formula using cross sheet references. I am not sure how familiar you are with any of those things. These should help

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Your formula will look something like this (the parts in bold being unique to your sheets:

=INDEX({reference to sheet 2 job locations}, MATCH([Job Code column in sheet 1]@row, {Reference to sheet 2 job codes}, 0))

Come back if you need more details.