-
Index/Match/Max Function Issues
@Paul Newcome @Andrée Starå I am having issues matching a cell value from my project status column. My goal is to reference my most recently updated (max modified cell) data entry and return that value in the selected cell. My current formula for reference: =INDEX([Project (Schedule) Status]3: [Project (Schedule)…
-
Correct IF Formula for updating "expiration status" dropdown based on dates
Hello Team. I am setting up a sheet that would help track and act on over 3K documents based on their end dates. The two column I would like to work together are "end dates" and "expiration status". The "expiration status" column is a dropdown with the following choices: "Current" "up to 1 year expiry" "6 mos. expiry" "90…
-
Formula for RYG Symbols in Child Rows to Update Parent Row
Hello, I'm wondering if there's a formula that could automatically update the parent row in a column that has RYG symbols? The parameters would be: -Green overall (in the parent row) if all children rows have green symbols? -Yellow overall (in the parent row) if any children rows have at least 1 yellow symbol? -Red overall…
-
Help with CountIf Formula
I need a count of # of rows based on data in 2 different columns. All data is on the same reference sheet. Here is the formula I have written that keeps coming back as unparseable: =COUNTIF({Global Talent Mobility Intake Range 1}, OR(@cell = "New", @cell = "On Deck", @cell = "Backlog"),AND ({Global Talent Mobility Intake…
-
How to Remove Duplicate Site Names??
Hey Smartsheet Community, I am trying to remove duplicated Site Names from my sheet and am running into some issues. Can someone please help me come up with a formula to accurately remove all duplicated site names? (Backstory: I copied a lot of data over and some of it is duplicated so we are trying to remove the…
-
Complex Formula Help Needed
On a Gantt chart, I've added a column % Progress Expected and I have it calculating % progress expected per day for each line item, based on the duration; see formula below. =(TODAY() - Start@row) * ([ADMIN: % EXPECTED DAILY PROGRESS]@row) * 1.1 The problem I'm trying to solve for is I need it to show 0% progress expected…
-
Week Number Troubles
Good Morning/Afternoon All, I have a client that we are counting the number of requests in a ticketing solution. Currently we are using this formula to count all the requests he gathers each week. =COUNTIFS([Request ID]:[Request ID], NOT(ISBLANK(@cell)), [Created Date]:[Created Date], WEEKNUMBER(@cell) = 7) Once we gather…
-
Add to a date if another column meets criteria
I'm not the best at the complicated formulas and need some help. I have 3 columns in play here. Submission Type (single select), Submitted date (automation added), and Due date (Date Field). If the requestor selects "Standard" in the Submission Type field, then I want the due date to be 30 days after the submission date.…
-
Formula help
I need assistance with a formula. Trying to create if/and statement for a checkbox. =IF(AND([Trigger for Parent]@row = TODAY(), [Submission Status]@row = ISBLANK, 1, 0)) If the Trigger for Parent column = today AND the Submission Status column ISBLANK Check the box
-
COUNTIF Between Date Range
Hello, I am trying to see how many projects were completed in in 2021. I have several different dates and years within the column, but I'm looking for a count of all projects with a 2021 date. The below formula is what I'm using but I am getting #UNPARSEABLE. =COUNTIFS([NOC Recorded Date]:[NOC Recorded Date], >=DATE(2021,…