-
Auto create incident number based on last name + number
I am trying to create a formula to take the first 4 letters from the Salesman Name column and add "-001", etc. to the end of the first 4 letters to create an Incident Number. My coworker created this formula and it's almost working, but isn't always taking the first 4 letters from the last name (see line 3 (incorrect) vs…
-
Primary Column Revision
Good evening We madea mistake when building our sheet. In the primary column we need to be able to have the "Child" rows equal what the "Parent Row" days plus the verbiage currently in the "Child" row. So we would like the "child" rows to say 140 Kendrick - Egress Stair - Approval Drawing.....etc etc . How do we do this…
-
Supply a count of a select status based on a date range, then supply a sum...
Good Morning SmartSheet Helper Gurus.... I have the need to get the number of Status = Closed for a select date range, then tell me the Sum of the Story Points column for those closed tickets. I can get my number of closed ticket with : =COUNTIFS({Story Point Reference}, FIND([Primary Column]2, @cell) > 0) I can get my…
-
Remove a Decimal in Column without Rounding
I have a column set up for a Retail Price, currently it looks like this: 3.99 I need to pull this column into another column with a column formula, but I need it to be formatted without the decimal, without rounding. So in the column formula I want it to pull in and look like this: 399. The column formula is automatically…
-
Calculating a date in the future
My use case: I receive reports that need to have an SLA tracked. From the date I receive the report, I need to calculate the due date. If a report has a severity of Critical I need to calculate the report date +60 days, if High, the report date +90 days. I have a severity column with CRITICAL, HIGH, MEDIUM & LOW, I want to…
-
MAX COLLECT Formula
I have one table for shipping (Production Priorities) : And another table for Project Revenue Recognition (Revenue Recognition Forecast): I would like to write a formula to auto-populate the max ship date for each project. =MAX(COLLECT({Production Priorities Range 1}:{Production Priorities Range 1}, {Production Priorities…
-
Can you combine an If(ISBLANK with OR
The formulas below work independently. I would like them to work together with an OR function to Check a box. Every combination I try is not working. =IF(ISBLANK(Role@row), 0, 1) =IF(Level@row <> 2, 0, 1) Any assistance you can offer is appreciated. Thank you!
-
Formula Countif then subtract then add
I'm working on a sheet where I need to count if the PEP was received. There are special circumstances where it may be sent back for corrections then resubmitted. I want to count when we receive it but then subtract it back out if it is sent back but then add it if it is received back again. I've tried several COUNTIF…
-
COUNTIF HELP
I'm summarising and want to count a Project Status columns Have a sheet per colleague and want to do formula that would add up how many On HOLD projects there in total across all of those colleague sheets?
-
Adding Months based on Calendar Days not individual days
I am looking for a formula that adds months to a date and keeps the "x" day of the month. Meaning if I am adding 3 months from February 1st, 2021, it would return May 1st, 2021 vs. just adding 90 days to February 1st and returning May 2nd, 2021.