-
Breakdown formula not working correctly
Hi All, So I've got this formula that automatically breaks hours down into respective columns based on dates: =IFERROR(IF(AND(VALUE(YEAR($[MDR Start]@row) + "" + IF(MONTH($[MDR Start]@row) < 10, "0") + MONTH($[MDR Start]@row)) <= [Jan-21]$1, VALUE(YEAR($Finish@row) + "" + IF(MONTH($Finish@row) < 10, "0") +…
-
Help with project metric and resource setup
we are not using Resource Management because it is way too manual of a workflow to manage. Instead I am trying to replicate some level of a resource map by doing the following: there is 4 stage gates that are the primary workload impact for various teams with a specific Start/End for each stage. Team 1 = Stage 1, Stage 2…
-
Nested IF formulas for status
I am trying to write a nested IF formula, but as I run it there is one argument that is not resulting as I expected. Basically what I want my Status column to represent is the following -Done: when a project is in 100% compliance -In Progress: when a project is anywhere from 1%-75% and its within the due date (the…
-
metric sheet and sumif formula
Hi I am having a tough time with a sumif formula on a metric sheet. =SUMIFS({Location # and Name 4}, LOCATION@row, {AP $ AMT POSTED}) I am wanting to count by location the $ in the AP $ AMT POSTED column. Any help is appreciated.
-
Shorter way to do this?
I am using a formula to change the value of one cells based on the values of some cells below it in a column. The formula that I'm using is working but I want to add it to some more places in the sheet, where it would get very long. Does anyone know a way I could abbreviate this? It is okay when there are only two cells…
-
Issue referencing VLOOKUP data in another formula
Hi, I have a VLOOKUP pulling in data from another sheet that will be updated on an ongoing basis. The data pulling in is the number of manufacturing days to complete an operation. The VLOOKUP function is working fine. This is the VLOOKUP formula: =VLOOKUP([Model Series]@row, {Mfg Hours by Model Range 1}, 7, false) Within…
-
Recommended roll-up scoring?
Hello all, I have a few different methods for scoring in Smartsheets but am wondering if there is recommended documentation or formulas, specifically, for scoring a parent status indicator (RYGB ball) based on its children. Of course, this depends on the definition of each status in context, but again I am asking for…
-
Need help with formula that shows both years and months
Hi, I need help with setting up column formulae for the two (yellow highlighted) columns. I have included the sum fields for each of the columns. Please suggest a formula so that one may see both the years and months of the grant in the column/ cell. For example for the given start and end dates, the Original Length of…
-
Count Distinct Values that only appear multiple times
I have a formula that follows this structure =count(distinct(collect(range, crit_range1, crit1, etc))) that allows me to count the number of unique entries that meet a set of criteria. Now I'm trying to modify that formula that allows me to count the number of repeat entries that meet a set of criteria. In the original…
-
Enter hyperlink to cell, if another cell is not blank
Hello, Is it possible to enter a hyperlink to a cell using a formula? I'm looking to have a column check if a cell is blank, enter "No info" if it is blank and if it isn't - enter the hyper link. =IF(ISBLANK([Provider A]@row, "No info", "<a href="https://weather.com/">Weather</a>") Thank you, Melitta