-
Sheet Summary - same sheet VLOOKUP with TODAY()
Hi, My Column Headers are: Date, Total Opened Cases, Total Closed Cases, Total Backlog. I am attempting, and failing, to use the following variations of a VLOOKUP formula. Can someone help?? =VLOOKUP(TODAY(), {Date}:{Total Backlog}, 4, false) = #UNPARSEABLE =VLOOKUP(TODAY(), {Date:Total Backlog}, 4, false) = #INVALID REF…
-
Please Add a Sum Product Formula
Please add a sum product formula to improve ease of calculations without workarounds.
-
Nested IF/AND/OR
I'm modifying a formula used for status tracking and I'm running into an unparseable error that I'm having problems resolving. I want to make the status red (stop sign) if the status is "in progress" or "not started" when the "at risk" column is checked. My understanding is that the formula will look for the first true…
-
Fiscal Year Starts in February sometimes end of January
Our fiscal years (FY), quarters (Q) and work weeks (WW) are giving me heartburn. I realize I need to create a table in order to capture each fiscal year. However, when putting in the formulas so far I am seeing issues with my Work Weeks and Fiscal Year columns. Part one (1) of my heartburn. For example, my work week is…
-
Sheet Summary COUNTIF error when summarising formula driven cells.
Hi all, I'm creating a sheet summary report with the intention of summarising when passed tasks were completed relative to planned completion data i.e. early, on time, late. To do this, I'm using an =IF formula (which works) to define how to label tasks in my "Completion" column based on whether the 'Actual Completion…
-
Calculating Avg Workdays w/ Conditions
Hello! I'm currently trying to calculate the average amount of workdays it takes for each person to complete a specific request on a metrics sheet from a parent sheet. For example: How long does it take me on average to complete a FOIA request? How long does it take me on average complete a litigation request? Request Type…
-
How can I show a colored health dot based on the other colored dots in the same column?
I am trying to have a cell show a colored health dot based on the other dots in the same column. This cell basically shows red if there any red dots, yellow if there are no red dots and at least one yellow, green if there are no reds or yellows, and blue if all other dots are blue. This cell called out should have a red…
-
Forumla help sought so cancelled tasks are not counted as at risk
This formula surfaces 'At risk tasks' to a project rollup dashboard report. It does so based on the criteria of due date in the past and status other than complete. =IF([End Date]@row <> "", IF(AND([End Date]@row < TODAY(), Status@row <> "Complete"), 1, 0)) Question - What needs to be changed so that it does not surface…
-
Start date auto update?
Hi, everyone. I'm very new to using Smartsheet and am having some troubles regarding start/finish dates. I'm trying to find and use the correct formula/automation to change the start dates if the previous job task has run over its allocated time. For example, I use Smartsheet to schedule trades for job sites, if a tiler…
-
Mutliplying mutiple columns + multiplying mutiple columns
Smartsheet does not like multiplying 0's so I need some help. I can get around this by adding more columns in but that will be painful with 13 similar sheets! lol There has to be a way to do this without additional columns. There are times when the MU columns are all 0, but there are REP gallons used. I get an "invalid…
