Does the dynamic view work with formulas?

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?
Answers
-
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.
-
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}))
The Dynamic View correctly filters by the current user, me.
Workaround for Multiple Contacts:
- For multiple contacts, a feasible approach is:
- Reference the Multiple Contact List in another sheet to extract the text of the list.
- Use a formula to split the list into individual names or emails by delimiter (e.g.,
","
). - Match these individual contacts with a Single Contact List in a lookup table.
- You can use these individual contacts to create a sheet filter based on whether any of them match the "Current User."
- 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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives