Best Of
Re: RE: Changing "yes" to percent
The formula for this will depend on your sheet set-up. I presume that there are two different columns that record the two answers and that they are next to each other, is that correct?
If so, we can use COUNTIF to count how many "Yes" answers there are. If there are 2, it's 100%. If there is 1, 50%. If there are no "Yes" answers, then it means both must be "No", so it's 0%.
Percents are decimals in formulas, so 1 = 100%, etc. You'll want to set the column with the formula to a % format.
Try this:
=IF(COUNTIF([Column 1]:[Column 2], "Yes") = 2, 1, IF(COUNTIF([Column 1]:[Column 2], "Yes") = 1, 0.5, 0))
The only instruction that may be tricky is your last statement, where you say: Yes or No=100% or 50%
If the formula above isn't correct, would you be able to provide a screen capture of your sheet set-up (but block out sensitive data), and explain how the last statement works?
Thanks!
Genevieve
Re: Timeline Variance indicators
Hi @Zain
There are two things to clarify with your formula:
1) What do you want it to do between the numbers of 7 and 15? Currently there is no statement for any colour between 7 and 15 so the cell will be blank, is that the correct response?
2) The reason you're getting an error is because the formula doesn't know the relationship between Helper 1 and Helper 2. Is this an AND statement (so both cells have to be in that range) or is it an OR statement (so if ONE of those cells is in that range it auto-does the first statement that matches).
I presume you want each of these to be an OR statement, so try this:
=IF(OR(ISBLANK([Variance helper 1]@row), ISBLANK([Variance helper 2]@row)), "", IF(OR([Variance helper 1]@row > 30, [Variance helper 2]@row > 30), "Red", IF(OR([Variance helper 1]@row > 15, [Variance helper 2]@row > 15), "Yellow", IF(OR([Variance helper 1]@row <= 7, [Variance helper 2]@row <= 7), "Green"))))
Keep in mind that if Helper 1 is 30 but Helper 2 is blank it will return blank, since that's the first statement.
Let me know if this gives you the desired output! If not, it would be helpful to know when you want each colour to appear.
Cheers,
Genevieve
Re: Dashboard chart from sheet
Thanks Mark,
I have created the intermediate sheet and that works well. I shall use this going forward.
Re: Timeline Variance indicators
Hi @Zain
1) Yes, you can identify predecessor rows and relationships in a Project in Smartsheet. See: Enable Dependencies and Use Predecessors
I would suggest reviewing this free Webinar that goes through Smartsheet's Project settings: SmartStart: Project Management
2) I don't know MS Project well so I can't speak to their functionality, but you can create what's called a Hierarchy in Smartsheet with Parent and Child rows (indented rows). See: Hierarchy: Indent or Outdent Rows. In a Project Sheet, the Parent row has a Parent Rollup Functionality.
3) This one is a little trickier without seeing your specific sheet/project set up. You can link cells together across sheets either with cross-sheet formulas or through Cell Linking, but it actually sounds like a Report would be better suited to your needs.
You can create a Report that brings in rows from multiple sheets (depending on your Plan Type), based on a filter (ex. is a Milestone). This would then show you all milestone rows from all of your sheets. Then you could Group it by the Task Name so you can see the duplicate Milestone names together.
See: SmartStart: Reporting and Redesigned Reports with Grouping and Summary Functions
4) Yes, you can create Reports and Dashboards out of Smartsheet sheets, including Project sheets. There are a number of Template Sets that show how you could configure this type of custom Dashboard... for example, see: Project Tracking and Rollup Template Set
Here's the Dashboard Webinar: SmartStart: Dashboards
I would suggest that the different SmartStart Webinars are a great way to get started, along with the different Learning Tracks in the Help Center (https://help.smartsheet.com/). If your plan has access to it, the Smartsheet University contains a number of eLearning courses that would be really helpful as well.
Cheers,
Genevieve
Re: What is the formula to flag duplicates from a multi-select drop down column?
Hey @sambittner,
You can make a list of all the available times, set the sheet to public, and create an automation that locks the row once someone has entered their name into the time slot. You could also set another automation to approve or deny the appointment which if denied would clear the slot and unlock the row for another person.
If you want to continue your original direction the formula you are looking for needs to contain the HAS function. The issue with that is you need to check for each time slot option so you would need the list of all times available either inside the formula or listed out somewhere else. I would break the sheet into two with the top portion containing all the times separately on single rows and the second portion for entering appointments. This way the top portion can check to see if any time is listed more than twice.
=IF(AND(COUNTIF([Time of Visit]:[Time of Visit], HAS(@cell, [Time of Visit]@row)) > 2, [Time of Visit]@row <> ""), 1, 0)
Re: Add Column to Row Report that was not in original sheet
The dynamic view solution is overly complicated, It would be nice to be able to add columns to reports for sorting and presentation purposes.
Is there a way to show a parent task on a row-sheet report?
I am creating a report, pulling from multiple sheets, that shows upcoming tasks in the next 7 days. The problem I am running into is that there is no hierarchy shown on the report. I get a list of tasks, but I do not know what 1) sheet they're from or 2) what phase/parent task they are related too.
Is there a way to show the hierarchy when reporting? ie)
Primary task
- sub-task 1
- sub-task 2
- sub-task 3
Primary Task 2
- sub-task 1
- sub-task 2
- sub-task 3
I want to thank you for the help in advance.
Re: Formula for IF cell is NOT BLANK and value < 'x"
Hi @Simon Potton,
NOT(ISBLANK(column@row)) is how you check if something is not blank.
You could use AND to combine both.
IF(AND(NOT(ISBLANK(column@row)), column@row<'x'), valueiftrue, valueiffalse)
Countif using Dates and another Range
I'm trying to use CountIf using the my auto filled date column and another column. The 2 parts of the formula I have come up with work independent of each other, but when I try to put them together I recieve the #INCORRECT ARGUMENT reply. Not sure what is wrong. Below is a copy of my Formula.
=COUNTIF([Request Create Date]:[Request Create Date], >=DATE(2018, 1, 1), [Issue/Nonconformance]:[Issue/Nonconformance], "Install Vendor Workmanship")
Re: How to create a request order form for multiple items/quantities
Hi @Angela B.
I hope you're well and safe!
I developed a solution using two forms (or the same form by using conditional logic) that can be used to submit the main information and then the other information on a new row for each submission after the first.
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!