-
Fiscal Year
Our organization works in a Fiscal Year Model (Q1AprMayJun Q2JulAugSep Q3OctNovDec Q4JanFebMar). I have a metrics sheet with a few YTD and Year Over Year calculations that leverage the YEAR parameter. Is there a simple replace that's recommended so that the formulas look for any particular year using the fiscal year? I'm…
-
Creat formula to evaluate date difference in working days
Hi All I'm looking for inspiration on creating a formula on separate sheet looking at data and dates from another. For this example, I have left on one sheet to demonstrate. I essentially have to complete all my projects 10 working days before Bom S Date starts and if not I need to flag up the QTY of projects and which…
-
Baffled......Circular / Blocked Reference (Vlookup)
This one is baffling. I have a problem with 2 sheets reading each others data through a Vlookup. Each sheet has Vlookup ranges from the other sheet. I understand why formula and Vlookup may return a #circular or #blocked reference, but I don't understand why my sheets work perfectly for periods of time, and then start…
-
Formula for showing subtraction/sum compared to total
Hi there, I'm working on testing quality assurance of products sent to my company. For each row in my Smartsheet represents one product I am testing. Each row requires testing on 160 samples. In my reports I want to show the "Result Summary" column where if there were no nonconformities, then it would show up as "160/160".…
-
Formulas being converted to values
Hi - I have two sheets where I have created formulas that return a percentage for child tasks based on hours estimated and date ranges that ultimately feed into the resource management view. The formula works as expected (initially), but when I close the workplan and reopen, it will (at some point), convert the formula to…
-
How to calculate days a ticket is open/closed
I am trying to calculate how many days a ticket remains open but I also need that formula to stop counting if the "Ticket Closed Date" column is filled in. I have tried a couple but they keep coming back as errors. There are multiple selections for the Status of Ticket column... Work Has Not Started, Work Has Begun,…
-
If Children Contain then display cell on that row in Parent Cell
Have children() that vary in range, children rows can vary from 1 to ~30. For each child that contains "Action" in column [Issue Category] display the cell Notes contents into parent row Notes cell. Currently using static formula. Issue is every time a row is inserted, I have to manually add to the formula. Current formula…
-
How to estimate hours per Task using % allocation, Assigned to and Duration Columns?
Hi Everyone, I hope you all are doing alright. We need to estimate how many hours will problaby be spent on each task. We already use %allocation, Assigned to and Duration columns in our cronograms. We can't use the suggested formula "%allocation * Duration * 8" because: 1) Multiple employees can be assigned on the same…
-
Dynamic numbering of feature list
I have a feature list that has a number associated with each of them (by row). I'd like to be able to reorder the list as I get input on their priority and have the number associated with it dynamically update. For example, if #5 moves ahead of #3, I'd like #5 to be #3 and the others below to reorder too.
-
If Status is one of two options, then checkbox is selected formula
I am trying to build a formula where if a status is either "Pending-Crim" or "Clear w/ Exceptions" then the checkbox will check, however I can't seem to get the formula to check the box once the status has been updated. Here is the formula I am using: =IF(COUNTIFS(Status:Status, "Clear w/ Exception", Status:Status,…