Best Of
Re: Keep getting "Selected data cannot be charted please select new range" error.
Charts require at least one column with numeric data. Depending on what you are wanting to display on the chart and how you may need to either create a report with grouping/summary options filled in or create a separate metrics sheet that contains the counts/labels in a table.
Re: Form is not working
they are being submitted by different users, all using phones and/or on desktop on Safari/Google Chrome
We have not recreated the sheet, but have recreated the form
Re: Automatic Predecessor Adjustment
Oh my God. I'm quite disappointed to read this. Two people have already raised this concern since 2019!
This is very valid and is a standard for any kind of gantt chart software. I was messaging support regarding this issue just prior.
- Missing Dark Mode
- Flakey keyboard support. On iPad Pro it keeps disappearing. Solution currently is to keep tapping on other cells or to quit and relaunch the app. Terrible.
- Auto updating of predecessor numbering as these other people have mentioned. Numbering SHOULD always automatically change. Predecessors SHOULD always be linked to a Task Row and that Task Row SHOULD be movable to any numbering or slot within the schedule. This will cause the Predecessor slot to change numbering automatically. Like for example, if it was on slot 5 and you added more rows to the sked, and it had to move to slot 8 then everything linked to it as Predecessor needs to change from 5 to 8.
I'm really shocked that this is not understood nor clear after reading the comment. Anyhow, I hope I helped explaining it and that this is solved because any serious software MUST have this feature. Imagine having 10 or more Predecessor dependent tasks! What a nightmare! Kindly have this fixed soonest. It's already more than half into 2020.
EDIT:
For those people like us 3 commenters bewildered by the app behavior. I just discovered that the app currently does do automatic cel identification in Predecessors BUT you have to tap on "update / refresh" icon located on the upper right hand of the screen on iOS. Still bonkers but at least it's there. Hopefully this will be rectified as we can't be expected to keep pressing refresh all the time as we make changes constantly.
Re: Automation: change cell value to a date when triggered
Hi @Patrikas
You're right, the Change Cell workflow cannot currently update a Date column. You can automatically record a date with a different workflow, but it set's Today's Date (see: Set the Current Date with Record a Date Action)
My suggestion would be to create a formula in that date column instead of using a workflow. You can use an IF statement to add a number of days based on the value in the column that says "contractor" or "permanent".
Try something like this:
=IF([Column Name]@row = "contractor", [Start Date]@row + 31, IF([Column Name]@row = "permanent", [Start Date]@row + 182))
It's easiest to add a number of days instead of dealing with MONTH and YEAR, but there are longer formulas that could add a Month instead of 30 or 31, it depends on what the column needs to be used for.
Cheers,
Genevieve
Re: User Management - Profile Pictures
Hi @hcosta52671
Hope you are fine, you can't add the picture for other users in there profile each user can add his own picture
Re: RE: Changing "yes" to percent
The formula for this will depend on your sheet set-up. I presume that there are two different columns that record the two answers and that they are next to each other, is that correct?
If so, we can use COUNTIF to count how many "Yes" answers there are. If there are 2, it's 100%. If there is 1, 50%. If there are no "Yes" answers, then it means both must be "No", so it's 0%.
Percents are decimals in formulas, so 1 = 100%, etc. You'll want to set the column with the formula to a % format.
Try this:
=IF(COUNTIF([Column 1]:[Column 2], "Yes") = 2, 1, IF(COUNTIF([Column 1]:[Column 2], "Yes") = 1, 0.5, 0))
The only instruction that may be tricky is your last statement, where you say: Yes or No=100% or 50%
If the formula above isn't correct, would you be able to provide a screen capture of your sheet set-up (but block out sensitive data), and explain how the last statement works?
Thanks!
Genevieve
Re: Timeline Variance indicators
Hi @Zain
There are two things to clarify with your formula:
1) What do you want it to do between the numbers of 7 and 15? Currently there is no statement for any colour between 7 and 15 so the cell will be blank, is that the correct response?
2) The reason you're getting an error is because the formula doesn't know the relationship between Helper 1 and Helper 2. Is this an AND statement (so both cells have to be in that range) or is it an OR statement (so if ONE of those cells is in that range it auto-does the first statement that matches).
I presume you want each of these to be an OR statement, so try this:
=IF(OR(ISBLANK([Variance helper 1]@row), ISBLANK([Variance helper 2]@row)), "", IF(OR([Variance helper 1]@row > 30, [Variance helper 2]@row > 30), "Red", IF(OR([Variance helper 1]@row > 15, [Variance helper 2]@row > 15), "Yellow", IF(OR([Variance helper 1]@row <= 7, [Variance helper 2]@row <= 7), "Green"))))
Keep in mind that if Helper 1 is 30 but Helper 2 is blank it will return blank, since that's the first statement.
Let me know if this gives you the desired output! If not, it would be helpful to know when you want each colour to appear.
Cheers,
Genevieve
Re: Dashboard chart from sheet
Thanks Mark,
I have created the intermediate sheet and that works well. I shall use this going forward.
Re: Timeline Variance indicators
Hi @Zain
1) Yes, you can identify predecessor rows and relationships in a Project in Smartsheet. See: Enable Dependencies and Use Predecessors
I would suggest reviewing this free Webinar that goes through Smartsheet's Project settings: SmartStart: Project Management
2) I don't know MS Project well so I can't speak to their functionality, but you can create what's called a Hierarchy in Smartsheet with Parent and Child rows (indented rows). See: Hierarchy: Indent or Outdent Rows. In a Project Sheet, the Parent row has a Parent Rollup Functionality.
3) This one is a little trickier without seeing your specific sheet/project set up. You can link cells together across sheets either with cross-sheet formulas or through Cell Linking, but it actually sounds like a Report would be better suited to your needs.
You can create a Report that brings in rows from multiple sheets (depending on your Plan Type), based on a filter (ex. is a Milestone). This would then show you all milestone rows from all of your sheets. Then you could Group it by the Task Name so you can see the duplicate Milestone names together.
See: SmartStart: Reporting and Redesigned Reports with Grouping and Summary Functions
4) Yes, you can create Reports and Dashboards out of Smartsheet sheets, including Project sheets. There are a number of Template Sets that show how you could configure this type of custom Dashboard... for example, see: Project Tracking and Rollup Template Set
Here's the Dashboard Webinar: SmartStart: Dashboards
I would suggest that the different SmartStart Webinars are a great way to get started, along with the different Learning Tracks in the Help Center (https://help.smartsheet.com/). If your plan has access to it, the Smartsheet University contains a number of eLearning courses that would be really helpful as well.
Cheers,
Genevieve
Re: What is the formula to flag duplicates from a multi-select drop down column?
Hey @sambittner,
You can make a list of all the available times, set the sheet to public, and create an automation that locks the row once someone has entered their name into the time slot. You could also set another automation to approve or deny the appointment which if denied would clear the slot and unlock the row for another person.
If you want to continue your original direction the formula you are looking for needs to contain the HAS function. The issue with that is you need to check for each time slot option so you would need the list of all times available either inside the formula or listed out somewhere else. I would break the sheet into two with the top portion containing all the times separately on single rows and the second portion for entering appointments. This way the top portion can check to see if any time is listed more than twice.
=IF(AND(COUNTIF([Time of Visit]:[Time of Visit], HAS(@cell, [Time of Visit]@row)) > 2, [Time of Visit]@row <> ""), 1, 0)