-
Complex Formula to return latest date a specific value was used and return a value from a dif column
Alright, I've got a pretty complex formula (at least for me..) that I've been banging my head against and could definitely use a little help from the awesome community! Here's what I am trying to do (see screenshots for reference). I'm attempting to create a formula in the "Latest Backlink Live" Column. I would like the…
-
Vlookup Function from Right to Left
Hello Everyone, Can someone help to understand how I can do a Vlookup from Right to Left. I am trying to add a unique ID to the right most column of two sheets and do a vlookup from Right to Left. While exploring this topic, I found solution to do it using CHOOSE in EXCEL however bit tricky in SmartSheet. Hope someone who…
-
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…
-
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…
-
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.
-
SUM and TODAY() function
Hello, I am building a formula to give me the SUM of the # of spots for dates called within the last 14 days. I was able to build a formula using the TODAY function: =SUMIF({# of Spots}, <TODAY(), AND({Date Calleds}, <TODAY())) However this formula returns a #INVALID DATA TYPE error. I added a number to the TODAY function,…
-
INDEX and MATCH with CHILDREN across two sheets.
Hi All, I'm having some trouble using INDEX and MATCH together to pull data from cells on one sheet across to another. I'm making an assumption that this is the best method however if I'm mistaken, please advise. I have a situation similar to the below where "MainName" is a Project Name and data beneath it is it's child.…
-
Index Match Max Date Formula
I have one table for shipping : And another table for Project Closeout: I would like to write a formula to auto-populate the max ship date for each project.