-
Calculating Time
Hi, I know Smartsheet is "working" on time stamps, but I need a little help. I am trying to calculate 2 different time scenarios. First is from the time a work order is opened to the time its is dispatched. Second is the time from the open date to completed date. You will see on my sheet, the "open to dispatch" time in…
-
Formula for date range
I am trying to calculate the total amount of invoices, from a given contractor, within a given date range. Im not sure how to write a formula to calculate within a specific date range. I have the formula to calculate the total invoices for an individual. But not sure how to insert date range into the formula. Here's where…
-
Sum if of Current Week +/- 1
I have a formula that will give me the current week capacity: =SUMIF(@{Week Number}, @cell = (WEEKNUMBER(TODAY())), {Capacity}). However I'm not sure of the formula to capacity for the next week or the past week capacity =SUMIF(@{Week Number}, @cell = (WEEKNUMBER(TODAY()) +7), {Capacity}). I would greatly appreciate some…
-
Checkbox condition in a child row, conditional formatting of parent?
I have an uber parent row that is the mother of a group of related tests for software QA. In addition, some of the children of that mother have their own children. I would like any parent row to turn red if any of its children fail a test, as noted by a "Fail" checkbox column, and bonus points for conversely turning green…
-
Help with rollup sheet structure and formulas
We have contractors that we pay every 2 weeks. I created a form for them to upload invoice info into. I also created a report that pulls the individual invoice totals. What i want to create is a rollup sheet that will give me a total of that week's invoices for a given contractor. So for instance...Total invoiced amount…
-
"SUMIFS=" " Circular Reference Error"
Hello, I figured out how to make work a large formula for =Sumifs. The idea was to add the "target budget" of all projects that would have as Project Manager (PM/RI) "Mari M." and only for the projects that would have an "Status of Request" of ( New, Work in Progress, In Design, In construction, Substantially completed,…
-
Parent Formula to display modified child status
I am looking for a Parent / Child formula to capture a status change (R / Y / G). The Parent status will change based on the Child status Change. For example: If I change the status of "Software" to Red (R) in the child row, it should change the Parent status to Red. The parent row captures any child row that changes from…
-
COUNTIFS + FIND function help
Hello! I am trying to count the number of rows with a non-blank cell in one column and a blank cell in another column. The non-blank cell contains a contract number that looks like 18-A-001-SH and everything but the leading 18 changes from one row to the next. I started with this formula but it is returning a value of zero…
-
Nested IF statement with Checkbox
I have 3 fields. 2 fields are checkboxes and 1 is text. Active (checkbox) Not Active (checkbox) Complete (Yes/No) I want to write a nested IF statement that if either are checked it provides a Yes / No response. IF(ACTIVE = TRUE, "Yes", IF(OR(NOT ACTIVE = TRUE, "Yes", "No"))) I get the field to work how I want for Active…
-
Auto populate score based on 6 different criteria zones.
Hello, I have been asked to build a scorecard that would automatically insert a numerical score (0-5) based on the input given. So for example, if a perfect score of 100 = 5; 90 - 99 = 4; 80 - 89 = 3; 70 - 79 = 2; 60 - 69 = 1; and 0 - 59 = 0, I would like to develop a formula that would automatically insert the correct…