Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Multiple, comma separated values in one cell

Michael
Michael
edited 12/09/19 in Archived 2015 Posts

Hi

 

I'd like to create a report which returns all rows that have a certain value in column 'Donors' - i.e. we have activities that are funded by one or multiple donors, and I'd like to return all activities that are funded by Donor X. 

 

Conference one - donor x, y, z

Conference two - donor y, z 

Three - donor x

 

And the report should only report lines with X. 

 

A simple column with text/number will treat comma separated values as separate values, and the report won't allow me to say 'field donor contains X'. 

 

Anyone know how to do that? Workaround? I see that for now, mulitple-assigned contact lists (for which this would be very useful too, without resource-utilisation even) is still in construction if at all

 

Thanks

Comments

  • Travis
    Travis Employee

    Rather then using a Contact List column, you could use a Text/Number column which WILL allow you to set a "contains" condition in the report builder (field donor contains X). From the sounds of it, you are not utilizing the functionality of the Contact List column, so you wouldnt loose any functionality by making it a Text/Number column. 

  • I could - however I want users when entering data to be able to pick from a drop-down list to ensure consistent data entry, and thus it would be cumbersome to have an item for each combination of donors, i.e.

     

    x, y

    x

    x, z, 

    x, z, y

     

    but maybe that is the way to go, thanks. 

  • Travis
    Travis Employee

    If you want to continue to use the Contact List column, you could add a Text/Number column to your sheet with a formula that pulls the data from the corresponding Contact List cell. You could then hide this column in your sheet then add it to your report and use the 'contains' function.  

  • Hi Travis - I'm not sure I understand. Can you explain what you mean by "with a formula that pulls the data from the corresponding Contact List cell"?

     

     

  • Travis
    Travis Employee

    Sure! Formulas can be used to pull data from one cell to another. If the donors name is in the cell Donors2 then add this formula to the corresponding Text/Number column:

     

    =Donors2

     

    Now, the Text/Number column will show the data from Donors2 and you can then use the "contains" function in reports!

  • MM
    MM

    Hi, I see your response and I have a similar issue. If I want the formula to read multiple numbers that are in 1 cell but it's not the same number each time because every row would has different info, how could I do that?

This discussion has been closed.