-
Formulas -Automated at risk status
Hi all, I'm quite new and I'm struggling with a formula I need to apply. I need to have an automated "at risk" status (red, yellow, green) in my projects masterlist. Red: % Done is equal to 0% and the Start Date has passed Yellow: % Done is bigger than 1% Green: % Done is 100% the formula I'm coming is this: =IF([% Done]1…
-
Calculating SUM based of criteria from multiselect.
Good day, I have a form with quite a few multi selects. After a user selects all that apply, I want to cross reference what he selected with a second sheet that has "costs values" associated to the same text that the user had available to him in the multi select drop down on the form. So basically after the user fills out…
-
Using CountIF function to calculate Count of Market
Hi! I'm looking to get some help on a CountIF formula. I have a sheet "Territory 4 Exit Interview Pivot Table" which I am looking to get a count of how many employees are in Market 41, 42, 43, and 44 (from the T4 Exit Interview Results sheet screenshot). I keep getting all sorts of errors when trying to use the CountIF…
-
Incorrect field value in email with Smartsheet automation
Hi, I see an issue in automated email if we use a Date column with formulas. In my sheet, I have a Date type column (CreatedDateTime) with formula =Created@row+"". So, When a row is added, this cell shows the Date and time based on timezone selected in Account->Personal Settings. e.g. 06/23/21 6:27 PM I am in GMT+5:30…
-
What value does MATCH return if it matches nothing?
Or possibly my question is what is the null value in Smartsheet. And does it matter if the context is boolean or not? Thanks.
-
IF Formula assigning scores
Hello, I have a formula (below) that assigns scores depending on a criteria and whether or not the checkbox is checked. I.e. If the Governor's Dashboard is checked, it will assign 4 points to the total project score. I have one item that I no longer want to be a checkbox (bolded below). I would like "Do you have required…
-
Counting checkbox's for children without counting the parent
OK I am new to building formulas like this and everything I keep trying gives me errors like "UNPARSEABLE" and "BLOCKED". How would I set up a formula for this? If the "Is there a color image?" box then the row will be counted. My goal is to have a total count of images that we have a color version for. the URL is on a row…
-
How to use MATCH function on multiple columns at the same time?
I've used the MATCH function before on the "City" and "CityMATCH" column. It worked out well. This is the formula that I used: =MATCH(City1, CityMATCH:CityMATCH, 0). I've now added another column called "Number" and "NumberMATCH." What I want to know is whether "3" in the "Number" column and "Chicago" in the "City" column…
-
Summarizing items by country
Hi there - I am working on a dashboard and would like to summarize content created in a region and then by country. Right now I have this set up manually with a formula based a static list of countries, but I am hoping there is something I could do that would pull the country list automatically from the column, as new…
-
Automate Harvey Ball based on % of checked boxes
Hi Smarties, Having a bit of a problem with this challenge. I would like to automate the [Progress] Harvey Ball's based on the % of checked [Completed] boxes in the children rows. If 0-24% of boxes are checked, the harvey ball should be empty. If 25-49% of boxes are checked, the harvey ball should be one quarter full. If…