Best Of
Calendar Tricks - Adding and Deleting Entries from Within the Calendar View
We use the Smartsheet Calendar view to manage all of our activities for a TV show.
The issue becomes Adding and Deleting events in the Calendar View as you have to to go back to the Grid view to do it.
Here is my Workaround:
To Add Events
- Create a Form attached to the sheet to allow the user to create a new entry. Make sure you configure it properly how you want it.
- Once done open the form and copy the URL
- Create a Column in the Sheet Call it Something like New Record
- Add the formula ="
{The URL You Copied}" with the URL pasted between the equals and quotes. It now puts a clickable URL in that column- Note: It might be easier to do this in a notepad and not type right into smartsheet. Then simply paste the formula into the cell. It can get a bit confusing trying to click the column to edit it..
- Now Right Click the Cell and select Convert to Cell Formula and it will copy the formula down.
Now when you are in Calendar View and you click Edit on the event there is a URL in each entry that you can click and it will allow you to add a new entry to the calendar via the form without going back to grid view!!
To Delete Events
First Create a Duplicate Calendar File
- There are a few different ways to do this but I prefer this method to create the Archive Sheet
- Copy the Calendar File by choosing SaveAsNew
- Name it Something like CalendarArchive
- Check Sharing Options
- Uncheck the Automated Workflows & Alerts
- Uncheck Everything under Sheet Data and Formatting
- Copy the Calendar File by choosing SaveAsNew
- Note: You could SaveAsNew and then open it and delete all the data.. JUST MAKE SURE YOU ARE IN THE ARCHIVE SHEET AND NOT THE MAIN SHEET
Now create the Column and the Workflow.
- Again create a column and call it something like "Delete Record"
- Chose Edit Column Properties and choose Column Type CheckBox
- Now open AUTOMATION > CREATE WORKFLOW FROM TEMPLATE
- Chose the "Move a Row to Another Sheet when Specified Criteria Met"
- Now Simply Choose when Delete Record is Checked Move the Specified Row to the Archive Sheet.
Now when you are in Calendar View and you click Edit there is a checkbox in each entry that you can click and it will "delete" the record you have selected.
Might take some time for the workflow to perform the action and update the calendar but my users know once they click it that it will eventually disappear
The good thing is that if a user accidentally "deletes" something you can go to the archive file and simply move it back manually by Right-clicking the row and selecting move to another sheet
** If you have been paying attention the archive sheet is missing the DeleteEvent Column.. don't worry smartsheet will automatically create it when it moves the first row over
Hope that helps some people !!
Brent Wilson
Re: AVERAGEIF Formula Help
When a column name has spaces, numbers, and/or special characters, you need to use square brackets around them in a formula.
Try wrapping each [Employee Name] column reference in square brackets the same way you wrapped the [Overall Satisfaction] column names in square brackets.
Paul Newcome
Re: Can I pull columns in different Sheets in one report as long as Primary Column is the same?
Hey @MMcLain
No problem at all!
So for a formula, I would have one sheet that lists all the unique IDs. In my example above it's the Name. Then I would use an INDEX(MATCH combination to search through Sheet 1 to find the same name and bring back certain cell data:
=INDEX({Sheet 1 Column A}, MATCH([Name Column]@row, {Sheet 1 Name Column}, 0))
You would need to do this for each column you want brought over, changing out what column you're indexing in the front of the formula for what to bring back:
=INDEX({Sheet 1 Column B}, MATCH([Name Column]@row, {Sheet 1 Name Column}, 0))
=INDEX({Sheet 1 Column C}, MATCH([Name Column]@row, {Sheet 1 Name Column}, 0))
Then when you get to a column that's in a different sheet, delete out the references and create ones that look at the next sheet instead:
=INDEX({Sheet 2 Column D}, MATCH([Name Column]@row, {Sheet 2 Name Column}, 0))
=INDEX({Sheet 2 Column E}, MATCH([Name Column]@row, {Sheet 2 Name Column}, 0))
=INDEX({Sheet 2 Column F}, MATCH([Name Column]@row, {Sheet 2 Name Column}, 0))
Here's more information:
Cheers,
Genevieve
Genevieve P.
Re: Can I pull columns in different Sheets in one report as long as Primary Column is the same?
Hi @MMcLain
Think of a Report as like a window to your underlying sheets. It does not combine rows together or merge content, but it can bring into one view rows across multiple sheets.
For example in your scenario, Jane Doe would have 2 rows in a Report. One row would have blank cells in columns I - M, and the other would have blank cells in columns A - F, since those columns don't appear in those Sheets.
For example, here is Sheet 1 vs Sheet 2:
See how I can't edit cell "E" because my Sheet 1 does not have this as a column:
However you could use the Report to Group by your Primary Column so you see all rows together, under the name as a heading. Here's an example:
Here's a free webinar that goes through Grouping and Summary: Redesigned Reports with Grouping and Summary Functions
An alternative is to use formulas, like cell-links, to bring through data from Sheets 1 - 4 into a master sheet, linking only the relevant information. Here's an article that explains a few different formulas you could use: Formula combinations for cross sheet references
Cheers,
Genevieve
Genevieve P.
Re: Dashboard - Page Break Line/Preview in Edit Mode
Yes, we do on occasion... we would likely print them much more often, but the formatting is a drawback.
In our case, we send weekly project updates. Those get circulated to other team members via email, are brought to meetings, redlined and sent back, etc.. Prior to Smartsheet, this was a manual word document with some info pulled in from our submittal tracker (excel) and our task list (MS Project or excel or whatever)
Now, we have a client dashboard set up for our projects that automate most of this. Submittal tracker and work plan (tasks) are in Smartsheet, along with a RAID log. But it's not well used because:
- as much as we like Smartsheet, its hard to get a client to visit a page for an update... they much prefer it handed directly to them in email.
- The dashboard lacks the density of information and rich text formatting (when the info is coming from cells in sheets) achieved through MS Word, and the formatting (page breaks) is a drawback. (Hopefully the new dashboard enhancements will help with the density of information.)
- Since no one likes the way it presents when printed, managers are still manually doing the project updates.
Tim Starkey
Re: New in Smartsheet: Critical Path and Duration in Minutes, Hours, and Weeks
The ability to set a specific start and end time was not included in this release but it is something we are tracking feedback for. Thanks for asking! 
Travis
Re: Index Match return "NO MATCH" when it should match
Try indicating an exact match in the MATCH function.
=INDEX({Range}, MATCH([Column Name]@row, {Range}, 0))
Paul Newcome
Re: Crosstab style reporting from Gantt data
Yes, this is an instance where the Pivot App would be helpful.
However there are some ways we can get similar outcomes without it. My first question is it if needs to be vertical like this. I ask as I'm wondering if we could achieve a similar result by using a Row Report, Filter the Report so you only see level 2 rows (lowest level), then Group by the Task Name column.
This would then give you header rows for each Group so you can quickly see percentages together.
For example, this is my source data, where I have 2 helper columns pulling the top level 0 Task into one column and then the second level 1 task:
Department Formula:
=IF(COUNT(ANCESTORS([Task Name]@row)) = 0, "", IF(COUNT(ANCESTORS([Task Name]@row)) = 1, PARENT([Task Name]@row), SUBSTITUTE(JOIN(ANCESTORS([Task Name]@row)), PARENT([Task Name]@row), "")))
Document Formula:
=IF(COUNT(ANCESTORS([Task Name]@row)) > 1, PARENT([Task Name]@row))
Then in my Report I've filtered so it's only level 2 showing, and Grouped first by the Task name then next by the Department Formula column, using the Summary to find an Average. See: Redesigned Reports with Grouping and Summary Functions
You could hide that "Department Formula" column since it's repeated to only have the Task Name showing next to the Document and the Percentage.
Would this work for you?
Cheers,
Genevieve
Genevieve P.
Re: Dynamic dropdowns - Waterfall request
I would love to create a drop down list that references a 'Master Table' of employees. Several of our KPI tracking sheets require selection of Employee Name and for every new hire class, I must open 8-12 different sheets to update the dropdown list with new names. For any sheets that use Form entry, I also update the logic so I know when a form user has entered an employee name that is NOT on the list to ensure all are represented.
My dream is to be able to create a master list of employees and have all my various sheets with the "Employee Name" dropdown reference back to that master sheet. With 2-4 new hire classes each month, it would be a huge time saver! Thanks!
BTW - we are on the Enterprise plan, no add-ons or extra features at this time.
Stacey C
Re: Over active automation
Hi @Gloydius
You can deactivate an automation without deleting it by using the 3 dots to the right of the automations. Maybe deactivate them all and turn them back on 1-by-1 to figure it out. Given the complexity of the automations you've shown, I would make sure you have plenty of Advil on hand before you start. 😊
In all seriousness though, if you're still having issues after that troubleshooting, reach back out.
I hope that helps. -Matt
Matt Johnson