Seniority Order Numbering
I have a union workforce and they rely heavily on seniority for scheduling and mandatory overtime, etc. I've developed a seniority tracker/calculator that keeps track of seniority through status and title changes. I can easily pull reports filtered and grouped by lists (Full Time RN, Part Time RN, etc.) for them to see their seniority. Unfortunately, once put in the dashboard for them to access, the row numbers from the report disappear. I need to use the dashboard view to strip formatting, which is used by management staff to see who's on leave or in training, etc.
The workaround seems to be a column to calculate their "seniority number" that would survive even as management sorts the source sheet every which way. I can't figure out the formula. I would need the numbering to be title/status specific (Full Time RN starting at one and then Part Time RN starting at one, etc.). The ordering is based on accumulated days of seniority, which is how they're sorted in the reports now.
Any ideas? (I have already voted on the addition of row numbering to the dashboard report views)
Answers
-
Figured it out (sure would be nice to have the ability to just show row numbers on reports in dashboards).
It took a helper sheet with two columns for each category and a column for the row numbers. One column has the Employee Number of the category (EMT Part Time) and the next column has the seniority days that correspond with the order. The cross-sheet references are all to the Seniority Calculator I mentioned in the OP. Here are the formulas:
[EMT Part Time]@row=IFERROR(INDEX(COLLECT({EE ID}, {Terminated}, 0, {Title}, "EMT", {Status}, "Part Time", {Seniority Days}, [EMT PT Days]@row), 1), "")
[EMT PT Days]@row=IFERROR(LARGE(COLLECT({Seniority Days}, {Terminated}, 0, {Title}, "EMT", {Status}, "Part Time"), [Row ID]@row), "")
-
Hi @RBro79
I hope you're well and safe!
I'm glad you got it working.
Great idea! That would be a great addition to Smartsheet features.
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!