-
RAG Range Formula?
Hi I have a RAG legend which looks like this-: 0-49% = Red 50-79% = Amber 80-100% = Green Like below I have a formula on my sheet which calculates a percentage on a monthly basis. I want the percentage to show in the next cell the relevant RAG e.g. 82% would be green or 55% would be amber. What is the best functions to use…
-
Remove absolute locking from a Range of Cells
I have a file, where I made the mistake of "Absolute locking" all of the formulas, before realizing that I am going to have to duplicate this chart other regions. Below is of "NED." I need to copy 2 more of these tables (for "central" & "west", ideally in the same sheet, so its easier to maintain down the road. As well as…
-
Need help with COUNTIFS formula
Hello! I have 2 fields, "Confirmed" (checkbox) and "Status" (drop-down values) to evaluate. In a 3rd field "# Waiting to be processed", I need a formula to return only the count of those records where "Confirmed" is checked and the "Status" field is blank which means "Status" has not been assigned yet Essentially, this…
-
SUMIF #NOMATCH Error
Hi, I'm running into issues when trying to use as SUMIF function in my sheet summary. I need to have it add together all the percentages"Capacity Rating." The goal is to be able to create a SUMIF for each project manager in the division so we can see what their workload capacity is before assigning projects. I've used the…
-
How to get an approval after a decline in the automated approval request workflow?
Hi All, I have created an automated workflow based on a form submission that asks for the Approval of at least 3 individuals/roles. If an Approver declines, it will send and update request to the originator (Project Manager) and ask them to update information in the form. Once the Project Manager has updated the form, the…
-
How does COLLECT() with String Criterion Work?
I have a simple formula (I thought) that seems to broken. It seems to be centered around the existence of a String in the Criterion Parameter of COLLECT(). Formula: =IF(MIN(COLLECT(Date:Date, [Work Type]:[Work Type], "Mandatory"))=Date1, Hours1-6, Hours1) Result: #UNPARSEABLE Is it not possible to have a string Criterion…
-
Calculate progress towards a goal
I'm looking at two things - one to highlight when a goal is not on target to complete by a date. The second is note a specific amount in a cell on a certain date. To clarify on the first - I need a formula that will tell me if the number in the Target AUA cell is not going to be meet by the date next to the 90 days cell…
-
What's wrong with this formula?
It returns only Complete or Not Started. =IF([% Complete]@row = "1", "Complete", IF([% Complete]@row = "75", "End in Sight", IF([% Complete]@row = "50", "Work in Progress", IF([% Complete]@row = "25", "Just Started", "Not Started"))))
-
VLOOKUP returning #INVALID REF
I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it. =VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false) Below is the excel sheet formula that does work but does not flow over to the smartsheet. =VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE) If you look at the excel…
-
Formula - Adding together certain assigned point values
Hello, I have created the below formula that works up until the bolded part. I am trying to add this to the below formula: If Less than 100 it gets zero points. If between 100 & 499.9 it gets 1 point If between 500 & 999.9 it gets 2 points If greater than 999.9 (or 1,000 or more) it gets 3 points. I am not sure how to add…