-
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…
-
Multiple IF Statements for Various Scenarios
Hello! Based on multiple criteria, I'm building a formula to yield a completion %: I want the results to yield 0% if a specific field is marked "No" or another field is marked "No" or "N/A", populate 50% (.5) if one of two criteria are met, and populate 100% (1) if all criteria are met. I started building the formula…
-
If And Formula question
Hi, I am giving myself a headache trying to resolve the issues I am having with this formula. Currently this is set up to change the results in a dropdown Status column based on dates in other cells, its currently working well based on the above, as follows; =IFERROR(IF([Actual Submit Date]@row = "", "Pending", (IF([Actual…
-
Formula to generate series of dates
Does anyone know how to write a formula to have a series of dates generated based on entry of a starting date? In excel, you can enter starting date of 5/1/23, the next cell can can be +7 days so the date is auto generated to be 5/8/23, 5/15/23 etc. I can't figure out how to do this to save time for user since they have to…
