-
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…
-
Count of Distinct Users in a Date Range
I am working on a metrics formula. It worked when I wrote it, I was using the data in a graph, which worked, I changed the graph type, and suddenly the data went to zero. Now, all o my formulas set up like this are all returning a value of 0. Trying to determine what went wrong. I am attempting to count the number of…
-
Parent/ Grandparent Cell Value
Hello- I am looking for a way to make every row return the same value as the highest level of its hierarchy (usually just children and grandchildren). I want it to report the top row in a column. In this example, I want all of these rows to return the value "Top Line" in a column.
-
Creating an Invoice
Good afternoon, I hope this makes sense. I know I have done this before in Excel but cannot remember how I did it. I am assuming it will involve some helper columns. I have a list of 100 people. These people can submit any number of timesheets for the month (though typically 0-4). They submit one timesheet per project for…
-
Return a value when one date is less than another
I'm trying to project the amount of non-bill hours we plan to spend on a project between the non-bill approval date and the contract end date. I'm getting an invalid operation error when using this formula: =IF([Column2]1 <= [Contract End Date]@row , [Approved Hours per week]@row , "") Here's my column setup: I've…
-
Average Collect Unparseable Error
I'm getting an Unparseable error with my average(collect) formula and can't figure out what's wrong with it. =average(Collect({Days to Approval}, {Level}, CONTAINS("Level 1", @cell ), {Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row )) All the references within "{}" brackets are columns…
-
SUMIFS Formula, Criterion change based on row
I am working on an attendance tracker that counts the points "Occurrence" someone "Employee Name" gets for missing work in a rolling 12 month span "Rolling 12 Months?". I am attempting to use a SUMIFS formula to create a running total "YTD" of the points someone has accumilated, using the criteria of their name, in the…
-
How to pull one column of data from one sheet to another and be placed in the corresponding box?
Hello, I have one sheet where information is collected for candidates we are looking to hire, and I need a specific column from another sheet of information to feed over to the candidate sheet in the column. The results that are fed over, must align with the correct candidate. Pictures below for reference. I tried…
-
Dashboard not updating
Hello Smartsheet Community! I created a formula that tracks the number of days away from the deadline that I posted on my dashboard as a visual for my students. =IF([Deadline Date]@row < TODAY(), "🔴 Past Due (" + ABS([Deadline Date]@row - TODAY()) + " days ago)", IF([Deadline Date]@row = TODAY(), "🟠 Due Today",…
-
OTIF
I am trying to calculate OTIF per month. I just added a Helper Column to identify Ontime and Late items. For example, would like to know Aug. 2025 On Time vs Sept. 2025 On Time. How can I achieve this?