Does the dynamic view work with formulas?

Siobhan16
Siobhan16 ✭✭✭
edited 01/16/25 in Add Ons and Integrations

I have one of my sheets set up so the program chosen references another sheet and generates a contact. I have created a dynamic view to restrict the view so only the contacts of those programs can see and edit their data. Unfortunately, it's not working. Is this because a formula with cross sheet references generates the contacts column? Is there a workaround to allow these contacts to see and edit only their program information?

Tags:

Answers

  • Paul Newcome
    Paul Newcome Community Champion

    What is your formula? Generally speaking, formulas can populate a single usable contact, but additional methods would need to be used if you want multiple usable contacts in a single cell derived from a formula.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @Siobhan16

    The challenge with using formulas and cross-sheet references to populate contact columns in a Sheet viewed by Dynamic View lies in how Smartsheet handles contacts. Dynamic View can filter based on single contacts derived from formulas, but additional steps are required for multiple contacts.

    Single Contact Usability:

    If your formula generates a single contact, Dynamic View filters can be applied effectively using the "Current User" filter.

    In the sheet image below, the Contact List values are obtained by a column formula from another sheet, as shown in the second image below.

    [Contact List]=INDEX({Contact List Sheet : Contact}, MATCH(ID@row, {Contact List Sheet : ID}))

    https://app.smartsheet.com/b/publish?EQBCT=2f5581ea837a4d2d8f17b0c53a12ea20

    https://app.smartsheet.com/b/publish?EQBCT=df754e95c5a0408796bb6b2184ec038c

    The Dynamic View correctly filters by the current user, me.

    Workaround for Multiple Contacts:
    • For multiple contacts, a feasible approach is:
    1. Reference the Multiple Contact List in another sheet to extract the text of the list.
    2. Use a formula to split the list into individual names or emails by delimiter (e.g., ",").
    3. Match these individual contacts with a Single Contact List in a lookup table.
    4. You can use these individual contacts to create a sheet filter based on whether any of them match the "Current User."
    5. Use the filter in the Dynamic View.

    In the first Sheet in the top image above, I used those formulas to get individual contacts from the other Sheet's multiple contact list.


    [Names] =INDEX({Contact List Sheet : Multiple Contacts}, MATCH(ID@row, {Contact List Sheet : ID}))
    [Name 1] =LEFT(Names@row, FIND(",", Names@row) - 1)
    [Name 2] =SUBSTITUTE(SUBSTITUTE(Names@row, [Name 1]@row, ""), Comma#, "")
    [Multiple Contact 1] =INDEX({Contact List Sheet : Contact}, MATCH([Name 1]@row, {Contact List Sheet : Name}, 0))
    [Multiple Contact 2] =INDEX({Contact List Sheet : Contact}, MATCH([Name 2]@row, {Contact List Sheet : Name}, 0))

    Then, I created a filter like this.

    Using this filter in a Dynamic View, I could filter rows by the current user in the contact list sheet's multiple contacts.

    While this process requires additional setup with helper sheets and formulas, it enables dynamic filtering based on multiple contacts. This method ensures Dynamic View functions as intended, even with complex contact list setups.