-
Auto-fill an overwrite-able value
Hi Community, I have a worksheet with a column (let's call it "FPD Factor") for calculating how many files can be scanned per day by a process. I would like for every new record created to have that column auto-populated with "70,000", but want it to be overwrite-able by someone. I plan to use the FPD Factor value in a…
-
If Index Match
Hi, I'm trying to figure out the due date for a submission if the dates remaining is >0 below is the formula I came up with, however it is not working. =IF(INDEX({Due Dates}, MATCH([Client Matter]2, {Client Matter #})), {Days Remaining}, @cell > 0) Also, is there a way to do an Index Match or vlook up that gives me the…
-
Help with Formula!
Hello, I'm pretty new to smartsheet but slowly working out how to create some functioning formulas! I wonder if anybody might be able to help me with the latest one please? I have multiple columns containing different data types (some are tick boxes, others are dates and some will say 'N/A' - I am trying to write a formula…
-
Formula to calculate project number based on group
Hi I want to calculate a project number based on what group it is in. In this example, there are 16 projects among 4 departments. I entered the 3rd column manually; i.e. the first digit is the department number and the second digit is the nth project for that department. What formula can I use to calculate the values in…
-
If criteria is xxx, duration is zero
Dear Smartsheet community, I have a sheet for staff to report time off. I have this formula to calculate the net working day(s) between begin date and end date. =NETWORKDAY([Begin Date]@row, [End Date]@row) However there will be occasion when staff will not be taken time off. Therefore, I want to add where column Absence…
-
Red Flag Formula
Hi All, Hoping to get some assistance with 2 questions. 1. In the Child rows, I would like the flag to go red when today's date is past the finish date AND % Complete is < 100% (ie the task is past the due date and not 100% complete). 2. The flag in the parent row (Black Circle) turns red IF any of the child row flags are…
-
"Last 7 days" filter is different than >TODAY (-7)
Hello, I need a sanity check: Following a crazy chase for a discrepancy in my sheet, I found out that if I create a filter "In the last 7 days", it starts counting yesterday as day 1. But if I calculate SUMIF is the option {Date} > TODAY (-7), it counts today as day 1. So, my final SUMIF tally is off compared to adding the…
-
Convert numbers to text & concatenate
Hi all, I'm new to Smartsheets and hit a bit of a wall. I need to put task due dates in a 30-60-90 designation. That 30-60-90 is set one time at the beginning of the month so it's not dynamic. I thought my best bet would be to build a column designated YYYYMM to do any month math for the rough 30-60-90 column. I figured…
-
# BLOCKED
Hi, Getting this error in only on one sheet. There are about 20 sheets, but only one throwing the error. Originally, the formula was more complicated, so I tried troubleshooting to see which function is broken. Right now, the only thing in the cell is "=COUNT([Task Name]:[Task Name])" All that is doing is counting all…
-
Looking for suggestions - master formula errors report
Hi all, looking for suggestions based on others' experiences. We have a series of dashboards and their respective back end sheets that occasionally throw formula errors for various reasons. I'm hoping I can leach some ideas from the Community on creating a master report/notification that informs us when any error occurs.…