I am trying to return a value based on one criteria.

Beginner to SmartSheet so I appreciate the help.

I have 2 Sheets:

  • Sheet A has all of the information
  • Sheet B will pull some information from Sheet A

In Sheet A I will have a 'build start date' and a 'test start date'. I want Sheet B to be able to pull in the Date based on status of the project (In Progress and Completed) into a column named 'Date'

Sheet A:

Sheet B:

I appreciate all of the help, i've tried using a INDEX/COLLECT formula but i cant seem to get it to work.


  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    edited 03/08/24

    You are looking to use cross-sheet formulas with my favorite INDEX/MATCH combo (INDEX/COLLECT can work as well), you can check out the Smartsheet page on cross-sheet formulas here. Just to confirm your phrasing, you'd want to pull in the data from Sheet A based on the "Project Name", it will need to be something unique to each sheet. Your formula would look something like this:

    =INDEX({Sheet A - Status}, MATCH([Project Name]@row, {Sheet A - Project Name}, 0))

    The references in the curly brackets {} are the cross sheet ranges that you'll create. To pull in another set of data, you would copy the formula, then replace the bolded reference above with the new range you'd like to return. Repeat until you have your dates and status.

    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!