Best Of
Re: Formula to create hyper link with display text defined
This would be a really great feature on our end as well; we use it in Excel all the time. Since Smartsheet is only on the web, you'd think a =HYPERLINK([URL], [DISPLAY TEXT]) would be a slam dunk here.
John Sauber
Introducing baselines for project management, available now!
Hi Community,
We are excited to announce the launch of baselines - a fixed reference point in time to measure and compare a project’s progress to completion. Capturing and comparing variances between the current actual schedule and the originally planned schedule is essential for tracking any deviations and allows project teams to quickly reprioritize to get projects back on track and delivered on time.
Licensed sheet owners and admins on Individual, Business, Enterprise and Premier Plans can create and edit baselines.
To get started, it’s quite simple:
- Set up a project sheet with tasks that have start and end dates (or use an existing one)
- In Gantt view, click the new ‘Baselines’ button in the toolbar
- Click the ‘Set’ button in the summary modal to set the baseline
- That’s it!
Three new non-editable columns automatically get added to your sheet: Baseline Start, Baseline Finish and Variance. You’ll see the baseline visualized as a thin grey line underneath the Gantt bars.
Users with the correct permissions mentioned above can toggle the baseline on/off, reset, and remove the baseline in the summary modal via clicking the ‘Baselines’ button again in the toolbar.
As dates shift around, the Variance in the summary modal will give you a quick visual snapshot of overall project status displayed as Days behind, Days ahead, or On schedule.
Read our help article for more information and get started with our updated setup dashboard for Project Tracking & Rollup template set.
Cheers,
Morgan
Morgan Kozol
Using a nested IF to determine a range
Here's a little tid-bit I just figured out today... You can use a nested IF statement to output ranges/cross sheet references that are to be evaluated.
The particular use case that led to this was an INDEX/MATCH where we wanted to INDEX on a single date column, but MATCH on one of 4 different columns depending on an Asset Type. We ended up being able to use a nested IF for the "range to search in" portion of the MATCH.
=INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, IF([Asset Type]@row = "Camera", {Asset Checkout Range 1}, IF([Asset Type]@row = "Support", {Asset Checkout Range 2}, IF([Asset Type]@row = "Lighting", {Asset Checkout Range 5}, {Asset Checkout Range 6}))), 0))
I personally like this a little better than writing out four different INDEX/MATCH statements and using IFERRORs to account for the #NO MATCH issue which would have looked like this...
=IFERROR(IFERROR(IFERROR(INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, {Asset Checkout Range 1}, 0)), INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, {Asset Checkout Range 5}, 0))), INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, {Asset Checkout Range 1}, 0))), INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, {Asset Checkout Range 6}, 0)))
We saved 93 characters/keystrokes and only had to reference the INDEX range and the "data to match on" for the MATCH function once each instead of 4 times, and I can't say for sure, but I also feel like this might be a little more efficient on the back-end as well.
I feel like there are a lot more practical applications too.
Here is a link to the thread that prompted this...
Paul Newcome
Re: Introducing baselines for project management, available now!
Firstly, congratulations to Smartsheet team for rolling out this much awaited feature! Thanks.
Now some feedback:
The Variation values are bit confusing as the Variation per the helper text is "Difference between Baseline End Date and Actual End Date".
A couple of things even before we get into the calculation issue.
Date definitions (Planned / Baseline / Actual)
Planned or Target Start/End Date is what we specify in the Start/Finish date columns when we create the plan.
Once we create/set the Baseline then whatever date values are present at that time become the Baseline Start and Finish Dates
Actual Start Date is the date when user updates the % complete to anything other and greater than 0 (aka task started)
Actual End Date is the date when user updates the % complete to 100 (aka marks the task completed)
I believe current Smartsheet implementation is not leveraging the "Actual" Start or End date as defined above, but the "Planned / Target" Start and End dates to calculate the variance.
Now coming to the variance calculation:
In a typical project management tool the variance is calculated as Actual - Baseline, however in current Smartsheet implementation it is Baseline - Actual [aka the Planned], and so it not showing the correct picture. For example, if the Baseline date was 5/20/21 and "Actual" [Planned] date was changed to 5/15/20, per Smartsheet logic the variance is 5 days, whereas in reality it should be 5 days less than Baseline which should be shown as -5 days. It should be the variance from the Baseline and not variance of the Baseline .
Sameer Karkhanis
Re: Email address (mailto: link) in Dashboards
Having this same issue. This thread is a little old, I'm hoping there has been some type of a fix for this that I haven't found yet.
Thanks
Kirt Tassmer
Re: Master and metrix sheets
Ok. It looks like you are primarily going to want to use COUNTIFS functions.
Here is some documentation on cross-sheet references that may come in handy:
Most of your formulas are probably going to end up looking pretty similar to:
=COUNTIFS({Main Data Sheet Site Column}, @cell = "Site Name", {Main Data Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {Main Data Sheet Checkbox Column}, @cell = 1)
This will count how many rows match the specified site, are in the month of January and year of 2021, and have that specific checkbox checked.
Paul Newcome
Questions about our ENGAGE 2021 Special Edition!? Drop them in the comments!
Join us online on Tuesday, June 8th from 9-10:30 a.m. PDT for a special virtual edition of ENGAGE!
You’re invited to ENGAGE ALL IN, a special virtual edition of our customer conference, where we’ll help business leaders like you unleash the potential of your workforce so you can deliver on the demands of today’s world.
This year, we’re holding two virtual events: register for the June 8th event here, and save the date for our Fall ENGAGE event on October 19th. More details coming soon.
Questions about our upcoming event? Post them in the comments below! We have so much to share with you and can't wait to connect with you all in June!
Jennie Ives
Re: Include Column Description in Update Request Form or Form to Update Existing Rows
These are both tremendous ideas and I would like to see both implemented.
+1 for the option to include column descriptions update requests.
+1 for a new type of form that can update an existing row; i.e. the option to have a fixed URL linking to a form that updates an existing row, rather than having to send update requests to do this.
Jules Petitjean
Re: The new Smartsheet experience
Please make the column headers resizeable vertically....the enhancement takes up 1/3 of the working space on the screen, I can barely see what I'm working on now.
It looks like the height adjusts to the char length of the column title and how narrow you've made the column, so when you have several narrow columns (like, for checkboxes) with any kind of heading title, it makes the vertical space huge and there's no way to adjust without shortening the column title or widening the columns. What was wrong with hovering over the column heading if it exceeded available space like before?
lhadduck
Re: The new Smartsheet experience
Hi all agree with all comments - you can reduce the annoying wide menu bar - far right look for the arrow up icon just below share button this grabs a bit of space back - but you then cannot see the name of your own sheet as that disappears too!! it does make you wonder if SS developers are actually SS users! like Jeff i am all in favour of improvements but not at the expense of usability - talk to users SS developers and not just the big Multi national users - we have zero support in the UK our local guy not interested and does not respond unless you are wanting to sign up for a training package of course!
Ashley Ryder
