Best Of
Re: Can I collapse/expand at a certain level of the plan?
@Andrée Starå Are you aware of an existing request for this functionality that I can upvote?
Re: Progressive totals working with column formulas.
A lot of stuff that SEEMS like it can't be done as a column formula (due to needing a reference to a fixed cell or a summary formula) CAN be - if you are willing to become a bit inefficient in digital space usage…
For example, you are setting up your sheet currently to simply not allow for column formulas because you are trying to do everything in a single column. Split it into multiple columns though (and include an indexing column "Row" that just includes the integer values for the row - there are other posts on here that note how to do that), and it will likely become possible. So - right now you are using a summary field to get the current surplus - if you can, rewrite that into a new column titled "SummaryCurrentSurplus" so that you have a column that simply repeats that value over and over and over. Then add a column next to that titled something like "ProjectImpactOnBalance" where you have a formula that just tells you, regardless of the balance, what that particular row DOES to the bance (add/remove hours). Then add a 3rd column titled "ProgressiveBalance" that says "Take the value in SummaryCurrentSurplus and add all the values from "ProjectImpactOnBalance" that are from this row and earlier rows and show me the total here" - this would look something like "=SummaryCurrentSurplus@row+sum(collect(ProjectImpactOnBalance:ProjectImpactOnBalance,row:row,< = Row@row))"
then, wrap all of those formulas with something to protect against errors (my personal favorite is an if statement tied to a required column, like the building name, like this "=if(buildingname@row="","",insertRealFormulaHere)" so that you just get empty cells once the required column is empty).
Re: Return 1st Day of Month for a Date
Wanda,
To confirm, you want the first day of the month for any date provided?
Assuming column is Invoice and row is 23:
=DATE(YEAR(Invoice23),MONTH(Invoice23),1)
Cheers,
Craig
Re: Counting summary data by week and month
Hello,
The automation piece mainly depends on whether you just need to display number for the current week, or if you need to display numbers for all past weeks as well.
You might consider replacing DATE with the WEEKNUMBER function: https://help.smartsheet.com/function/weeknumber
=SUMIF(Date:Date, WEEKNUMBER(@cell) = 1, [Number of Inspections]:[Number of Inspections]))
You'd need to add this function to all rows as needed, and you'd likely need to consider using something more like this instead if your sheet has projects that span multiple years:
=SUMIFS([Number of Inspections]:[Number of Inspections], Date:Date, WEEKNUMBER(@cell) = 1, Date:Date, YEAR(@cell) = 2018)
If you're wanting to SUM the current week, you might consider something like this:
=SUMIF(Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Number of Inspections]:[Number of Inspections])
Gantt View Duration Zoom Levels
Now that we can add sub-second durations, please allow a zoom level that can view such durations. I need to visualize processes that are on the order of milliseconds.
Re: Hours and Minutes as timeline feature in Gantt View
You can place the suffix H after your duration unit, but the smallest increment on the Gantt timeline is unfortunately DAYS.
I would like to see this changed also.
Data Mesh: Stop feeding Deleted Rows to Data Mesh sheet
I was told that once rows from a source sheet make their way to the Data Mesh sheet, there is no way to remove them. Even though the rows may no longer exist in the source sheet, you can't prevent them from showing up again in the Data Mesh sheet. My source sheet (report) is pulling from multiple projects so it's not feasible to know what rows were deleted by project managers. I have no way of knowing what they deleted in their sheets feeding to the report, that then feeds to the Data Mesh sheet. Just want to be able to report accurately that rows in the Data Mesh sheet ARE active/live and not old rows that were deleted. Anyone have thoughts on this? Hopefully Smartsheet can develop a way to prevent this from happening in the future.
Re: Sharing a sheet- view access internally only
Try publishing with Read Only-Full option. This option provides you the cability to limit access to internal users.
More Robust Backup Platform
There have been many requests for a more robust Data Backup/offload system because the existing system is inadequate.
Smartsheet is simply not a viable option for data management in regulated spaces until:
1. Backups are more easily automated (Can't you just make a Data Shuttle checkbox to prevent replacement of previous exports instead of the old export being deleted every time?)
2. Exports that include attachments automatically reconcile attachments to the rows they're associated with instead of just dumping them in another folder (we are manually reconciling dozens of attachments every month by renaming them to add the row number)
These aren't just quality of life requests. They are making me hesitate before recommending this platform to other teams as a solution because these flaws create unnecessary compliance risks.
If you do not intend to implement a resolution to this issue, as is suggested by the fact that this user's suggestion took a year to even reach the product wishlist, please make that clear and we will seek other data management options.
Thanks for hearing and prioritizing this concern.
Re: Report Grouping
I came here with the same problem. It seems the multiple choice dropdown is the culprit for my report also. Which is annoying as I still want to group my data by this column, but I cannot change the data to only allow one choice in the dropdown.