Best Of
Re: Matching 2 Criteria in Source sheet to reference 2 other Criteria in Target Sheet
Hi @RotFraught
You can use the COLLECT function to create a range of lead time data that matches your criteria.
=IFERROR(INDEX(COLLECT({source sheet : Leadtime}, {source sheet : Supplier}, [HV Breaker Supplier]@row, {source sheet : Supplier INDEX}, [HV Breaker Supplier INDEX]@row), 1), "")
BTY, your question is a model example with clear explanations, real-world examples, and visuals, making it easy to understand and answer. It sets an excellent standard for asking structured, helpful questions.
Re: Pre-Built Chart Color Schemes
Yes. AND there be a standard color chart for Color Blind. I struggle with having exec managers who are color blind so the charts lose their value.
Re: Project template sets - weighing options
I think you would be better off using the PMO template if you are wanting rollups of multiple projects into one dashboard. It's what I use to manage around 60 projects. Project Tracking & Rollup is good for one project. With the PMO template, each project gets a Project Tracking & Rollup, but it also gives a way to consolidate key information to one sheet, the Intake Sheet.
Purpose of the Intake Sheet is to log key information on all projects to one sheet. It's basically a list of all projects with information you want to track on one sheet. This enables a rollup dashboard for all projects (which is included in the template). In our case, we use a form for project submittal that routes for an approval process. The form comes from the Intake Sheet and loads information to the Intake Sheet. You don't have to use an approval process.
Using the PMO template, each project gets a project folder with a dashboard for the project, Project Tracking & Rollup.
The Project Metadata sheet is used to extract certain fields from the individual Project Sheet and to pass the same fields to the Project Intake Sheet. It provides a method to have real time updates to the Project Intake Sheet from each individual Project Sheet as the project is updated.
Once you understand how it works, it takes less than 5 minutes to setup a project and link it back to the Intake Sheet. Within the structure of the PMO, you can add fields that you need and do summaries to enhance the dashboard.
Good Luck!
Re: #INVALID OPERATION with INDEX(COLLECT
There must be something wrong with the [Meter Reading (Gallons)] column then.
Make sure all filters are off. Then create a new filter on that column and start it out where that column "is one of". Then it should give you a dropdown to select from. See if there are any error messages listed in that.
Re: Sheets and Form and User want to View specific sheet
Good morning!
You can share view only access to users a single sheet from within the Sheet you're sharing. (Open the sheet, and click the Share button.) These would be users who are NOT shared to the workspace as a whole.
If the users you want to share view-only access with need to be shared to the workspace, and will need higher access for other items (e.g., they need access to edit access for other sheets), best practice is to set the workspace access to the lowest setting needed. In your case, you'd set the workspace as a whole to View only, and then would need to add edit access for specific items to each user for whom that's appropriate.
Does that help?
Re: RAID Risk Matrix
This is a bunch of IF statements in the [Risk Level] Column
= IF(AND(OR([Risk Likelihood]@row = "1 Unlikely", [Risk Likelihood]@row = "2 Possible"),[Risk Severity]@row = "1 Acceptable"), "Low",
IF(AND([Risk Likelihood]@row = "1 Unlikely", [Risk Severity]@row = "2 Tolerable"), "Low",
IF(AND(OR([Risk Likelihood]@row = "3 Likely", [Risk Likelihood]@row = "4 Almost Certain"),[Risk Severity]@row = "1 Acceptable"), "Medium",
IF(AND(OR([Risk Likelihood]@row = "2 Possible", [Risk Likelihood]@row = "3 Likely"),[Risk Severity]@row = "2 Tolerable"), "Medium",
IF(AND([Risk Likelihood]@row = "4 Almost Certain"),[Risk Severity]@row = "2 Tolerable"), "High",
IF(AND([Risk Likelihood]@row = "4 Almost Certain"),[Risk Severity]@row = "3 Undesirable"), "Extreme"
Remove the paragraph breaks between all of these and make it one long formula and you should be good to go!
Re: Can I have a form populate two different sheets?
Never mind. I figured out that I could set up a form to populate a dummy summary sheet. Then I set up an automated workflow to move rows from my dummy summary sheet over to the appropriate summary sheet and auto-number rows within the appropriate summary sheet. It's slightly clunky to have a dummy summary sheet sitting around, but it gets the job done for sure.
Re: Contact List Automation Not Working
Hi @M_Nabulsi
It looks like everything’s set up right with unrestricted permissions and the contact list, but if notifications aren’t sending, try these quick checks:
- Make sure the emails in “Stakeholder” are valid and registered in Smartsheet.
- Have contacts check their spam folders for the alerts.
- Confirm there’s no Smartsheet service outage.
Always here as a resource.
Best,
Re: RAID Risk Matrix
We could index a table if you had it built out in a Smartsheet, but if you prefer to use a nested IF instead of indexing a table, it would look something along the lines of:
=IF([Risk Severity]@row = "4 Intolerable", IF(OR([Risk Likelihood]@row = "4 Almost Certain", [Risk Likelihood]@row = "3 Certain"), "Extreme", "High"), IF([Risk Severity]@row = "3 Undesirable", IF([Risk Likelihood]@row = "4 Almost Certain", "Extreme", IF([Risk Likelihood]@row = "1 Unlikely", "Medium", "High")), IF([Risk Severity]@row = "2 Tolerable", IF([Risk Likelihood]@row = "4 Almost Certain", "High", IF([Risk Likelihood]@row = "1 Unlikely", "Low", "Medium")), IF(OR([Risk Likelihood]@row = "4 Almost Certain", [Risk Likelihood]@orw = "3 Likely"), "Medium", "Low"))))
Re: Notifications via email
i @Elvis G
If notifications aren’t sending, ensure you have an automation set to trigger notifications specifically for the “Delivered By” field upon form submission. Double-check that the “Delivered By” field is a Contact List with valid email addresses, and confirm that recipients are recognized users in Smartsheet.