-
Formula not working
=IF([ME %]5=N/A,"",IF(AND([ME %]5 > 0,[ME %]5 <= 0.1),"Yellow",IF(AND([ME %]5 <= 0,[ME %]5 > -0.1),"Green",IF([ME %]5<-0.1,"Purple",IF([ME %]5 > 0.1, "Red" Does anybody know why the above formula doesn't work? I keep getting (Unparseable) in the cell. Help. Thank you in advance. DS
-
Update requests for Sheet Summary Fields
Hi Community, hope you are well. Does anyone know how and if it is possible to set an automation that triggers update requests for fields in the sheet summary? The update request function generates an handy form which would be perfect to capture the weekly project status update which as per current set up is located in…
-
formula to pull data from one column and combine duplicates
This is probably something pretty easy and in excel I would use the consolidate function. I just need a formula to pull data from one column to another, but I need duplicate names left out
-
Update Request - Accumulated hours formula
Hi All, Currently I am setting up a tracker for tracking weekly hours spent on requests that come in via a form. I would like to set up an update request that runs every friday (until task is marked as closed) to the assigned person who will enter the amount hours they spent on the task in the week just gone. I would like…
-
Unparseable "IF" Formula
Can someone help me understand what is 'unparseable" about this formula? =IF(AND(Finish21<=TODAY(), STATUS21=2)), "RED", "GREEN" Whereas I have a finish date in column "Finish" and a checkbox complete in column "status" and I want to create a red color dot if the date is past and the status is unchecked, otherwise a green…
-
Can I use an INDEX formula to retrieve value from another INDEX Formula?
Hello, I have two similar INDEX formulas, where one works and the other one retrieves #INVALID VALUE error. They are as follows: "IF([Nov-19]188 = 0, 0, INDEX($[Jan-19]$119:$[FY50]$119, 1, [Nov-19]188))" - this one works and retrieves the value I want. "=IF([Nov-19]110 = 0, 0, INDEX($[Jan-19]$189:$[FY50]$189, 1,…
-
Setting a range using a hierarchy
Hi, I've got an INDEX function that looks up values in a group of rows under a parent in the same sheet. =INDEX(ID$3:$Goal$183, MATCH(ID214, $ID$3:$ID$183, 0), 5) Instead of having $183 I'd like to use a function to set the range to everything under the parent (in row 3), no matter how deep it is. Is there a way to do this?
-
Select separate values from multi-select cells
I have a sheet where staff input their hours available in a week. I have the formula to calculate the hours available for the week. However, staff can enter multiple start and end times, if they are available from 8:00am - 10:00am and then from 3:30pm to 5:00pm. The formula still works for the first set of numbers, but…
-
Counting Unique Instances
I have an inventory of courses and course masters in a Smartsheet. A course (e.g., CAP 600) can have one or two modalities (BL or blended, OL or online, or BL and OL). The PARENT row is the prefix, and each child is a row for a course. Oftentimes there are two rows for each course because there are two modalities of the…
-
Row and Sum calculations for Capacity Management
So, until we get 10k feet, I am trying to figure out how much of our team's capacity is spent on projects or operations work. In the sample below, I'd like to know how much time teams and sub teams spend on Projects, Operations, Enhancements, and Admin. There may be muliple project, operations, or enhancement rows. I'm…