Best Of
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
Re: Sum up workload for sum tasks
You would need to put a formula in every parent row and just use
=SUM(CHILDREN())
Paul Newcome
Re: Symbol Flexibility - more symbol colors/options
I use emoji's to add additional colors when needed. See example below:
=IF(Status@row = "Not Started", "⚪", IF(Status@row = "In Progress", "🟢", IF(Status@row = "Complete", "🔵", IF(Status@row = "At Risk", "🟡", IF(Status@row = "Behind Schedule", "🔴", IF(Status@row = "Task Deleted", "❌", IF(Status@row = "On Hold", "🟣")))))))
Mariann Carmen
Re: Using Parent Rows in Reference Another Sheet Formula
After reading your comments and reviewing my setup, I realize that I was getting a false positive on my equation. Because my numbers column was getting a total count from its children, and my sumifs was looking for the name buncombe, and getting the results from the adjoining cell in the numbers column the parent formula section of my logic was basically being ignored, it was still looking at every row in the reference. (Interesting that it was not returning an error). As soon as I named one of the subtasks the same as the parent it got added to the sumifs total, thus making the number higher than actual.
JamesB
Re: Smartsheet tabs in MS Teams
@sheetsmartsarah, thanks for the detailed reply. What you detailed in extra step is what I want to achieve but the problem is I want the sheet to open in that view without user interaction. At the help page https://help.smartsheet.com/articles/2482422-rename-modify-settings-remove-items-microsoft-teams
it talks about the option "Show in Full Tab View" which is as described above but the problem is that option doesn't seem to be there anymore.
Teams already has branding/logo so I just want as much project data as possible in the tab window, the logo bar is actually distracting and redundant. In this post (https://community.smartsheet.com/discussion/92658/export-smartsheet-from-teams) @Genevieve P. even mentioned that feature as a fix but it appears to have been removed.
Steve_Mitchell
Best Template For Inventory/Material Tracking
What is the best Template in Smartsheet to track inventory going from warehouse to field and then the unused stuff returning to the warehouse.
jcormican
Re: Version Control/Backups That Maintain Cell Links
I just ran into this as well. I've been maintaining a "non-prod" version of my workspace for several months but when I tried to save it as a new copy I got the dreaded message that I can't because I have more than 100 items. I've been touting this as a great solution within the company and had no idea of this limitation. If it's not resolved quickly we'll likely have to move our solution to another tool as others have stated.
Amanda Fulbright