How to display the value of column A once a value is found on column B?

Hello All,

I'm new on smartsheet and I'm having difficult to find (or express myself the correct way) articles that are displaying what I need. So I need your help.

I do have a master sheet with several pieces of information. On a Column A I do have customers names. Column B I do have a multiselection cell where these customers have informed events they would be participating.

What I do need to make it happen is:

Once a customer have selected Xmas on Column B on my original sheet, another sheet would pull the name of the customer found on Column A from the original sheet to display it on my second sheet.

Is there an easy way to make it happen?

Thanks in advance.



  • Michael Pappas
    Michael Pappas ✭✭✭✭

    You could set up an Automated Workflow that copies the row from Sheet A onto another sheet depending on the value in Column B; however, that would copy the entire row.

    If you don't want all of the data from Sheet A to go to Sheet B, only the value in Column A, then you could do the following (I have no idea if this is the easiest way) on Sheet B:

    1. Create an Auto-Numbering column that numbers the rows 1, 2, 3, etc. called Row ID
    2. In the Customer Name column, enter this formula: =INDEX(COLLECT({Column A},{Column B},"Xmas"),[Row ID]@row)
    3. Convert it to a column formula

    You'll have to set up the cross-sheet references for {Column A} and {Column B} to Sheet A.

    The formula will COLLECT the values in Column A for which that row contains "Xmas" in Column B. Then it will INDEX what was collected and return the values in order because the Row ID should match the Row Number. So the 1st value in Column A that has "Xmas" in Column B will be returned in Row 1 on Sheet B and so forth.

    NOTE: The formula will always return the values in the order that they are in Column A on Sheet A. This means that if you reorder the rows on Sheet A, the values in this column on Sheet B will reorder. The rest of the row will not.

    NOTE: It will also only pull over the number of rows that have been created on Sheet B, so it's not fully automatic.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!