-
Want to flag overlapping dates AND times
Based on information I've found here, I've created a formula that identifies overlapping times that occur on the same day, but I'd like to adapt it to incorporate items that occur over multiple days. This will be used for booking of equipment. Here is what I have so far: =IF(COUNTIFS(SAP:SAP, SAP@row, [Start Date]:[Start…
-
Change project status in Parent Row
I would like to change the parent row automatically if the status is changed other than "Planned" & "On Hold" in the status column, Please help me with the formula to achieve this. if all the tasks are completed it should change the status to completed otherwise it should be "in progress"
-
Stuck on IF, AND, and SUM formula
I am trying to create a semi-complex formula. I have two check boxes. When someone clicks check box "A", it sums up specific columns. When someone clicks check box "B", it sums up the other columns. When someone clicks both "A" and "B" check boxes, it'll sum up all of the columns. I am starting with this (I've edited the…
-
Index Collect Error
Hello! I am trying to use an INDEX COLLECT function to get a number value. It uses the following: Total number Filter of the Month Matches names between two sheets This is the formula I am using and it is returning an Incorrect Argument Set error. =INDEX(COLLECT({OPK Monthly Report 22-23 Unfilled 0-3}, {OPK Monthly Report…
-
Count Down Formula
Hi All, Does anyone have a formula that will count down to a specific date? Thank you.
-
Drop down on dashboard pull all matching rows
I am trying to build a dashboard that will allow someone to enter a Asset# and it will pull all rows from a sheet that match that Asset#. The Primary Column is a Inspection number that contains the asset# and date. There is a separate column that is the Asset#. It would be even better if it would show it from youngest to…
-
Formula that counts the total of red dot symbols in my status column
Hi everyone, I have a project plan with several rows. There's a STATUS column in which traffic lights colours (gray, green, yellow and red) appear depending on the closeness of the task to its deadline. In another sheet, I'd like to link a cell to the STATUS column and use a formula that counts how many red dots there are…
-
Converting =SUMPRODUCT from Excel to Smartsheet
How would I convert this formula to work in Smartsheet? =SUMPRODUCT(($B$93:$B$137=$A144)*(J$93:J$137)*(1-($G$93:$G$137))) B93-B137 = Project Type J93-J137 = [July thru 7/31]$125 I can get the first part using a SUMIF formula: =SUMIF([Project Type]$83:[Project Type]$125, =$[Project Type]@row, [July thru 7/31]$83:[July thru…
-
Formula Calculation with a zero value denominator
I'm trying to create a formula to calculate the # Hours per remaining week based off the total HOURS remaining. Calculation takes into consideration the calculated remaining # of weeks on a project. Logic should be: Take Remaining hours / # of remaining weeks. If remaining weeks is < 1, simply return the amount of…
-
Problem w/Index - Match formula
I using the following formula, =INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}), 0) And it works for the first row when i input in the contract number into the column. On subsequent rows when i input the contract number it pulls back incorrect information and if I put in a fictious number it pulls…
