Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Index IF formula for data in Row bases on Column data

    ✭
    in Formulas and Functions

    Essentially I’m trying to move rows and its data from a master sheet to a separate sheet based on the yes/no answered on a cell within the row. I keep getting #unparseable. Possibly make it automatic transfer.

    I am unable to use the Automation workflow due to formulas and cross references etc.

    thank you

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭✭✭

      Hello @Marielitos can you share your formula as well as some screenshot?

      Melissa Yamada
      melissa@insightfulsheets.com
      Data made simple, spreadsheets reimagined

      · Share on FacebookShare on Twitter
    • ✭

      The information is in a source sheet, I have a column with YES/NO, the only rows i want to to get copy are the rows with YES in that column.

      image.png

      The destination sheet is the same but I need the rows and its info to get copied automatically if the column contain the YES.

      INDEX/MATCH formula with an IF statement to conditionally return the data: =IF([Column with "Yes/No" value]@row = "Yes", INDEX([Sheet Name]::[Range of data to copy], MATCH(@row, [Sheet Name]::[Column to match against], 0), 1), ""). 

      Im not sure if I need to do a continuous formula to transfer information just from some columns or is it easier to just do the entire row.

      Thank you

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      Hello @Marielitos

      If you're going through the path of using formulas, you'll need to create the formula on each column on your destination sheet. Also, I think you'd want to use INDEX/COLLECT functions instead of the INDEX/MATCH since you have the criteria of Yes/No.

      So something like this for every column:

      =INDEX(COLLECT({Column X},{Unique Identifier},[Unique Identifier]@row,{YES/NO Column},"Yes"),1)

      Another easier way is just to do the automation and copy the row when the Yes/No column is equal to "Yes". Formulas and cross sheet references won't be copied over to your destination sheet only the values/ data will be copied over.

      Melissa Yamada
      melissa@insightfulsheets.com
      Data made simple, spreadsheets reimagined

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions