-
Report on Last Comment date?
I'm seeing that the System Modified field is NOT updated after entering a new comment on a row. Is there a way to report on the Last Comment Date? I am trying to identify a way to create a report on my sheet that shows Last Update dates, and Comments are being utilized to provide a status update, or entry of the last…
-
Formula COUNTIF(Children) with multiple conditions
In Smartsheet, there is a function called Countif(CHILDREN([reference])). This is a great formula but what if I wanted the count of children based on more than 1 condition? I searched in the knowledge base, but couldn't find either a 'countifs(children)' formula nor a workaround. Is there a workaround? Example In this…
-
Filtering values by V-lookup based on multiple column values
Hi, i'm trying to filter values without YES in Column2 of sheet Y by Vlookup from Column2, Column3 of sheet X where Column3 is tagged as YES. Here is the formula: =COLLECT([Column2]1:[Column2]8, {New Sheet Range 1}, @cell=Value<>Yes). I feel something wrong with my code or approach. Any help appreciated.
-
Sheet utilizing "=Today()" in the formula is no longer updating the Report or Dashboard
I have a sheet where we do calculations such as Tasks coming due in the Next 7 days "=COUNTIFS({AF}, <=TODAY(+7), {PComp}, <>1)". When we used the metric widget on the dashboard, it didn't update the numbers each day. So we changed and used a Report to capture the columns/numbers from the sheet, and the report widget to…
-
JOIN COLLECT formula
Hello, I could use assistance building a JOIN COLLECT formula, I think. Here is what I'm trying to do. Using the formula =JOIN(COLLECT(rangecriterion_range1criterion1[criterion_range2criterion2...]) , I don't understand what I'm collecting and what I'm joining. p.s. I already have the -SUMIFS in place to do the math from…
-
Calculating average difference in dates between two columns
Hi I am trying to find the average time to complete a task without adding additional columns in my sheet. I would like to set it up in the sheet summary for easy reporting. In excel I would use the following formula. If a cell is blank the row will not be calculated as part of the average.…
-
Duplicate Formula Not Working As Expected
Hello, I have two columns, one has job numbers in it, and the one beside it has this formula to detect if the first coiumn has duplicates: =IF(COUNTIF([Complete Job Number]:[Complete Job Number], [Complete Job Number]51) > 1, 1). The row number--51 in my example formula--changes for each row. It doesn't turn on when I have…
-
Force auto fill formula to increment from the top of sheet
Hi, I have a sheet with project workflows. Each workflow has an ID# and they have indented rows. I have a form that enters new rows into the top of the sheet. Everything is working fine as far as auto-filling all the gnarly formulas I have in various cells, except I have one issue: Currently, I have to assign the ID#…
-
SUMIF by Vendor and Date
I am new to Smartsheets and cannot figure out the formula for a particular SUMIF. Basically, I need the total by Vendor & Year. I have an incredibly long list of invoices that I've imported into Smartsheets, and want to be able to use a dashboard off of the Sheet Summary to report on the totals by Vendor for each of the…
-
Summary
I am trying to find a way to do a monthly summary of the number items each month in Published, and Expired Alert Status. Does anyone know what would be the best way to complete this? Thanks!
