Best Of
Re: Happy 10th Anniversary Community + Giveaway!
Smartsheet is like Excel on steroidsβitβs got the grids and formulas you love, but turbocharged with automation, collaboration tools, and project management.
AnemonePoppy
Re: Happy 10th Anniversary Community + Giveaway!
Smartsheet is the tool to make you look good at work! π
Bruce Case
Re: Formula Issue
@kmercer Absolutely. No problem at all. Just remember the way the If statements work is it will search for the first true statement then continue on.
Mark.poole
Re: Some parts of the automation not running based on Index(Match) cell change
@kowal oooooo, so it's not a bug, it's a premium feature prompt⦠This makes so much sense. I do have Data Mesh but I was hoping to avoid premium features for this build since I am trying to create a template for unit managers with regular licenses. Thank you so much for your help.
vakhanka
Re: On Track/Off Track
Good morning, @Kasey Mabary. I think I was able to create what you are looking to do. Please let me know if this is not the direction you were looking to take your response.
In order to make this work you are going to need to introduce an OEC Status Helper Column and then one "Helper" column for each OEC Status grouping you have. So in your example above you shared "OEC - Kickoff" and "BLD - Build In Progress" as two phases. Each one of those will get a helper column to calculate the value of your checkboxes. This helper column, once programmed, can be hidden from view.
Below is the spreadsheet I made with your data. You will see that the formula automatically assigns "OEC - Kickoff" to the OEC Status column if the total number of checkboxes is 5 or less. This would be the equivalent of checking off every box under the Kickoff options. Then it automatically assigns a value of "BLD - Build In Progress" to the OEC Status if the check boxes total 6 thru 10. Which would be the boxes correlating to the next phase, plus having completed all of the kickoff phase previously. It then assigns a value of "No" when the check box total is LESS than 5 and status is "OEC - Kickoff", or when the total is less than 10 and the status is "BLD - Build In Progress." It assigns a value of "Yes" when the OEC Status is "OEC - Kickoff" and the total number of check boxes is 5, etc.
For the "On/Off Track" Column it is a Text/Number column with the following column formula:
=IF(AND([OEC Status]@row = "OEC - Kickoff", [Kickoff Helper Column]@row = 5), "Yes", IF(AND([OEC Status]@row = "BLD - Build In Progress", [Before Build Helper Column]@row = 5), "Yes", "No"))
For the OEC Status column you will need to replace your dropdown with this column formula in a Text/Number column:
=IF([OEC Status Helper Column]@row <= 5, "OEC - Kickoff", IF([OEC Status Helper Column]@row <= 10, "BLD - Build In Progress"))
For the OECE Status Helper Column (which you can hide) use the following column formula, which you will add more helper columns to for the other categories you have:
=[Kickoff Helper Column]@row + [Before Build Helper Column]@row
For the helper columns for Kickoff Helper and Before Build you will use these formulas, and again, need to add the same type of formula for any other categories you need a checkbox section for:
=COUNTIFS([Kickoff - SP Status Updated]@row:[Verify OE Wizard is Disabled]@row, 1)
=COUNTIFS([Before Build - SP OE Type Updated]@row:[Before Build - SP Docs Received Task]@row, 1)
I hope this helps! Please let me know if you this is not what you were looking for, or if it is and you need help expanding the formulas.
Regards,
Brian
Brian Wilson DC
Re: Has anyone developed an organization-wide solution for Joint Commissions readiness?
Hello @Cathy Salscheider ,
I am happy to carry on to assist you.
Many, many, many years ago I built a full Commission tool from Access (SQL) database and Excel (VBA). This is way before the age of Advanced Tech (e.g., Azure, AWS, JIRA, SNOW) and ICMs (e.g., Callidus, Xactly, Varicent, CaptivateIQ). Since then, I have stood up Xactly, Callidus and Varicent. Best advice is to get comfortable with ICM continuous improvement and resource push/pull.
Below is what I have built in Smartsheet.
1) Annual IC Plan Component Review and Change Requests
2) Annual IC Plan T&Cs Review and Change Requests (e.g., IC, Legal, HR)
3) IC Tool Learning Center for Administrators (can be scaled for End Users)
4) IC Ticketing Tool for Administrators (can be scaled for End Users)
5) IC Tool SPRINT Platform Migration and Continuous Improvement (DevOps SSOT)
The question you have to ask yourself is "Am I ready for an IT or Finance Audit?". Any deviations (High/Medium/Low) will require repair and have a short period to implement that repair. Public Company readiness has extra bells and whistles.
Are you IT Ready?
1) US GDPR Compliance
2) EU GDPR Compliance
3) Non-US and Non-EU Compliance (e.g., LATM, APAC)
4) Local Country or State Work Councils (e.g., France, Germany)
5) Company Data Security and Privacy Standards
6) Environment (e.g., DEV, QA, PROD) Access Audit Logs
Smartsheet Valuable Attributes for Consideration
1) Data Shuttle to consume external data (e.g., HRIS, CRM)
- OneDrive, SharePoint, Box
- PowerAutomate has some viable functionality but is limited
2) DataTable to assign unique RecordIDs for data ingestion and audit trail
- Filter large data sets at ingestion or ad hoc
3) DataTable connection to Smartsheet
- Supplements Activity Log search target restriction of Collaborators only; instead of Field for Field entry combo logic.
4) For Simple graphics, Sheet Summary or Reports to build Dashboard Widgets
5) For Complex graphics, Data Shuttle Offload into PowerBI AND pull in as Dashboard Web Content Widget
6) Forms are great for the below and can automate scalable drop-down options.
- Innovation requests
- Focus Group Testing of innovation
- Ticketing tool
7) Smartsheet Automation is great for Notifications, Proofs, Archiving. You must make sure you track limitations.
- For example, a Smartsheet has a limitation of 500K records. Building an Archive Automation requires monitoring.
1) Gather Requirements
- Administrative costs
2) Assess Requirements vs Smartsheet Functionality
- Prebuild DevOps costs
3) Assess Requirements vs External Functionality to Supplement Smartsheet Pitfalls
- Prebuild DevOps costs
4) Assess Additional tech stack or people costs
- Prebuild DevOps costs
5) Identify Timeline for Build SPRINTS vs Resourcing
- Build DevOps costs
6) SPRINT Reporting
- Administrative costs
7) Focus Group for End User Beta Testing
- Administrative costs
8) Change Management
- Postbuild DevOps costs
- Do not let perfection prevent action. Great is the continuous improvement of basic functionality.
9) Rollout Strategy (e.g., Communications, Change Champions, Adoption Evangelists, Enablement, Hypercare)
- Administrative costs
- You can use the same Focus Group
Re: Unparseable Error
Right away with out looking into it too much. You have a syntax error. SUMIFS does not work with OR and AND statements. Instead you would have to do two separate formulas and add them together.
=IF(Name@row <> "", (SUMIFS({Expense}, {Period}, 1, {Type}, "RES602782") + SUMIFS({Expense}, {Period}, 1, {Type}, "RES602497")), "")
See if this fixes the problem for you.
Mark.poole
Re: October Question of the Month - Join the conversation and receive a badge
I am not scared of taking a risk or trying something new because, as Wayne Gretsky said:
"We miss with 100% of the shots we don't take."
Sing C
Re: March Question of the Month - Join the conversation and receive a badge
Making new friends and being a good friend to the ones I have are skills I would like to improve upon. I've worked from home for many years now so making new friends is really hard.
Mary Clark
Re: March Question of the Month - Join the conversation and receive a badge
I would love to be able to go back and pick up piano lessons. I started that a long time ago, and as I got older, it got replaced with swimming, college activities, and just life in general.
Definitely playing the piano for me! πΉπΆ
Stephanie_G