Best Of
Re: Is there a way to attach an uploaded photo(s) to a specific column instead of the whole row?
I actually find it easier to add photos on a mobile. They can just click into the field on the mobile app form, take the photo or open their photos, and save. The photo will appear in the cell on the sheet.
KPH
Re: Help with WEEKNUMBER formula
Hello @Xochitl C.
I'm happy to help. To help me clarify - were you expecting the formula above to produce the combined week number and month text string, or was that formula meant to do something else?
To produce the combined text, try this. As written below, it will produce WK 1 JAN2024, which might be needed if your sheet will span multiple years
="WK" + " " + WEEKNUMBER([Audit Submission Date]@row) + " " + IF(MONTH([Audit Submission Date]@row) = 1, "JAN", IF(MONTH([Audit Submission Date]@row) = 2, "FEB", IF(MONTH([Audit Submission Date]@row) = 3, "MAR", IF(MONTH([Audit Submission Date]@row) = 4, "APR", IF(MONTH([Audit Submission Date]@row) = 5, "MAY", IF(MONTH([Audit Submission Date]@row) = 6, "JUN", IF(MONTH([Audit Submission Date]@row) = 7, "JUL", IF(MONTH([Audit Submission Date]@row) = 8, "AUG", IF(MONTH([Audit Submission Date]@row) = 9, "SEP", IF(MONTH([Audit Submission Date]@row) = 10, "OCT", IF(MONTH([Audit Submission Date]@row) = 11, "NOV", IF(MONTH([Audit Submission Date]@row) = 12, "DEC")))))))))))) + YEAR([Audit Submission Date]@row)
If you to not need the year as part of the textstring, delete the bold text from the formula above.
Will this work for you?
Kelly
Kelly Moore
Re: Report doesn't show data for others
@Xochitl C. Hi, most likely they are not shared to the underlying sheet(s) used to populate the report. If you do not want to share the sheets then publish the report or display it in a dashboard.
Razetto
Re: Conditional/customized formulized data validation
Happy to help!
Unfortunately, it's not possible now, but it's an excellent idea!
Possible workarounds
- Using a form, and so-called helper columns, If project a, show column 1, b, show 2...
- Using conditional formatting and messaging in the sheet + helper columns. If project a, highlight and show message for column 1, b, show 2...
Make sense?
Would any of those options work/help?
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
Andrée Starå
Re: Indexing and collecting information from non adjacent columns in original sheet
So glad you got it working.
As you continue to grow your formulas, consider the good practice of renaming the generically assigned cross sheet reference names from smartsheet. For instance, instead of 3. Safety Investigation Range 7, you can rename the Range 7 before you click the insert reference button to reflect the name of your actual column. This will help you troubleshoot your formulas as you can see by the range name more clearly in the text.
Let me know if there's anything else I can do for you
Kelly
Kelly Moore
Re: Allow Forms to be Saved to a Workspace
This would also help to copy the basics of a form as well when using the same setup for different sheets or workspaces.
Stephanie Graham
Re: Use a formula for subtraction between two columns
Hi @Megan.elh
Please see if this returns the value you expect:
=[Contract value]@row - [Current spend]@row
Column names that contain spaces must be entered in square brackets. Smartsheet will do that for you when you type the equal sign, then select the first cell of the equation, type the minus sign, and select the next cell.
Entered on the row you wish to calculate. It can be converted to a column formula by right clicking the cell and selecting "Convert to Column Formula".
Hope this helps!
Ker9
Re: Dashboard refresh
Ok. Set up two separate automations. The first one will lock the rows at 1am and the second will unlock the rows at 2am. The lock/unlock should update the TODAY function without you having to manually activate the sheet.
Paul Newcome
Re: Dynamic view for person & team
@Neil Watson I've just tested out another option that might work for you.
Set up a separate team list sheet with a row per team and a multi-select contact column for the team members.
On your task sheet you'll need a single-select contact column for your assignee, a column for your team name (text/number is fine) and a multi-select contact column for the team. Assignee should be selectable. Under the team name column add the following column formula:
=INDEX(COLLECT({Team list Name}, {Team list Team}, HAS(@cell, Assignee@row)), 1)
Note, here 'Team list Name' is the name of the team and 'Team list Team' is the multi-select contact column with the team members.
Under the multi-select contact column in the task sheet add the following column formula:
=INDEX(COLLECT({Team list Team}, {Team list Name}, [Team Name]@row), 1)
This should pull through all the contacts for the relevant team and you should then be able to set up a Dynamic View that is filtered on that column that gives you your 'My Team's Tasks'.
Let me know if you get it up and running.
Philip Robbins