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.
Restricted view in dynamic view. I want to use multiple e-mail addresses to restrict access.
I have created a reference sheet where per account the e-mail addresses are joined i.e. =JOIN(CHILDREN([Primary Column]@row); SUBSTITUTE([carriage return]@row; "-"; "")), In the Carriage return column I have a - CR-.
I have multiple sheets that are using forms to receive data. Based on the entered data, the "account" field in the form is used in a VLOOKUP looking for the account in my reference sheet and use the field that contains the joined e-mail address and copy it in field "Account access e-mail"
a workflow checks in the sheet if an answer is valid and if not copies the row to an action list.
The action list is linked to a dynamic view and the dynamic view is linked to a dashboard. The dynamic view is restricted for users in the "Account access e-mail" field.
So far so good, but for some odd reason the e-mail address that i copy over to the actionlist are not recognized as e-mail address. whatever i use as a separator, space, "-", CR, " " it is not working.
What am I doing wrong?
I must say I have had more problems with e-mail addresses that are not recognized as e-mail address.
Can anybody help me with this challenge?
Thx
Hilbert
Answers
-
It is currently not possible to use a formula to output multiple usable contacts in a single cell. Please do Submit a Product Enhancement Request when you have a moment as this would be a really great feature to have.
Additionally, you say that your carriage return is not working. Have you turned on text wrapping in that column? Another note for this... You can get rid of the helper column and use CHAR(10) instead.
=JOIN(CHILDREN([Primary Column]@row); CHAR(10))
-
solved the problem using HEX 0D as a CR in the formula.
=JOIN(CHILDREN([Primary Column]@row); " ")
-
Which problem exactly? Outputting multiple usable contacts or just the carriage return?
-
The outputting of the multiple e-mail now works, with the CR the e-mail is recognized as e-mail address in the multiple address field, I'm happy I solved it myself, ;-) thx
-
Is it a single email address, or do you have multiple usable email addresses in the same cell? If the second... Can you please explain more on "HEX 0D" as well as the rest of your solution such as what is in the Primary Column?
This is something that many users have been trying to accomplish for quite some time now, and if you were able to solve this particular riddle it could be quite a game changer...
-
Hi Paul,
It is multiple email addresses, i had it working but for some odd reason smartsheet is now not recognizing the e-mail address in the multi field. And as such the restricted view function in dynamic view is not working. I need to restrict the users of the report based on which account they are working for. so i created a reference sheet with account and employees, using the above mentioned formula with a 0D separator to add a Carriage return so every e-mail will be on the next line. The column setting is contact and allow multiple contacts in field. The data is then copied using vlookup into multiple sheets. The dynamic view restriction is looking at the same field to determine who can have access to the specific data. I just don't understand why smartsheet is not recognizing the e-mail addresses.
If I toggle the allow multiple contact per cell off, save and then switch it on, Save again it recognizes the e-mail. i need to do that for all the sheets everytime to get the restricted view to work.
Is there a different separator required or is this a bug?
-
This is unfortunately a known issue. Please feel free to report it to Support as a "bug" as well as fill out a Product Enhancement Request letting them know you would like the ability to use a formula to output multiple USABLE contacts in a single cell.
-
Hi Paul, Where can i do that. I'm really getting frustrated. I have build a smartsheet access granting process, that requires e-mail adresses and then i use that for the dynamic view access, sometime it works and when i refresh the sheet the e-mail adresses are not recognized anymore and as such no access to dynamic view. I need this urgently to be compliant to GDPR. is there a way to speed this up?
-
Thx for your support
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
