-
Issue with leading zeroes when Identifying duplicates
I am using the following formula to identify duplicate invoice #s in a spreadsheet. I've noticed that the formula does not work when the invoice # starts with a zero. It works for all other formats: text, numbers (without leading zeroes), dates. The column property is set to text/number. What could be the issue??…
-
Formula to Turn Number into Date
Hi Smartsheeters! We currently have another system feeding dates into Smartsheet in a number form. For instace, this number (1695686400000) is sent to Smartsheet represents 9/26/2023. How can we convert that number into a date value in Smartsheet? Thank you for your help!
-
Requesting help reporting top three vendors for three locations based on a common review database
Our team is trying to build a tool to intake vendor reviews based on five averaged criteria to inform vendor selection on future projects. I've built the form, a review collection database, a review metrics helper sheet, a vendor masterlist and a preliminary report. I'd like to be able to consistently report the top three…
-
Formula question - cross sheet reference with multiple criteria
Hi all, I'm trying to calculate the total registration fees from confirmed people =SUMIF({In-Person Registration Status}, "Confirmed",{Attendee Individual Cost}, > $0.00) I have several needs to create cross sheet references that are looking for specific criteria and creating sums so any guidance would be much appreciated.
-
How to add column that shows start/finish time in minutes?
I see you can list a task in minute increments, e.g. 15min. We are creating a cutover plan and need to list a start & finish TIME: e.g. 9:00, 10:00, etc. Does Smartsheet have this as an "intelligent" column or just a text/number one only? Would be extremely helpful, so that folks can time the work and also, if you add…
-
Formula to identify the task name of next child task due
Hi! I've looked at past posts to try to figure this out, but nothing seems to work. I'm trying to get a formula to work that will show me, in my parent task row, the Task Name of the child task that is due next, and then the due date of that task. In my screenshot example, i would want the formula to return Task 5, and its…
-
Need formula syntax help resolving unparsable error
So, not going to lie, I had to resort to chatgpt for this one after spending 2 weeks, banging my head on this (Got in over my head skill-wise with this one). I've already resolved a few issues I identified using answers to other questions here, but now I'm stuck. I think it has something to do with the nested OR() AND()…
-
Stumped: COUNTIF Formula for 4 criteria and 2 ranges.
Hello! This is my first time utilizing the Smartsheet Community 😊 I am trying to develop a report that highlights the number of requests across four different categories (RFPs, New Programs, Funding Rounds, and Rolling Applications) within four fiscal quarters. So ideally, I want a formula on my sheet summary to tell me:…
-
Formula to identify the task name of next child task due
I'm trying to get a formula to work that will show me, in a specified row, the Task Name of the child task that is due next. Formula: =IF(AND(COUNT(ANCESTORS([Task Name]@row)) > 0, isParent@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN([End Date]@row),…
-
Setting Up If Statement with 3 Parts
I have a status column where I want the 3 options to be "Not Started, Work in Progress, or Complete". The "Not Started" status would come from the Comments & Date Closed column being blank. The "Work in Progress" status would be based on there being comments added, but the Date Closed has not been filled in. The…
