-
Nested IF & VLOOKUP for Checkbox
So I'm trying to write a formula to pull a date from Sheet 1 if a checkbox is checked and match it to the same email on Sheet 2 . =IF({Sheet 1 Range 1} = true, VLOOKUP([Column2]@row, {Sheet 1 Range 2}, 3, false), "") Occasionally Smartsheets gives me trouble with pulling information from date columns that I've set to pull…
-
Formula to Find the Start/End Timeline
Hello SS Community, I'm trying to find a formula to insert in the sheet summary that brings the earliest Quarter and Fiscal Year (FY) and the last/furthest quarter and FY. Thank you in advance.
-
VLOOKUP only works with the raw data, not with another sheet that uses the same source
Hi all, I need your help to understand why VLOOKUP is not working in a seemingly peculiar case. But it might just be a difference between Excel and Smartsheet, as I'm a Smartsheet newbie. I have two reports, originally it was built like this: Report 1 - draws from original raw source data file, output column 10 Report 2 -…
-
Combining Multiple Formulas
Hello, Trying to help a co-worker out with combing these formulas into 1. She thinks adding AND is the solution but i was not sure. Here are the formulas broken out below that we need to make into 1 formula. =COUNTIFS({IPT Consolidated Backlog Range 1}, @cell = "Sales Operations", {IPT Consolidated Backlog Range 2}, @cell…
-
Complicated IF Formula
Hi, I am looking to do a formula that will let me display the red, yellow, or green symbols depending on the amount of answers are green, yellow, or red. The picture might let me explain better. The left column is Status and the right column is Constant. Only the top 3 Totals will count towards the result. Total NYIOP…
-
Only show calendar text (Primary Column) if other column = X
Hi, I’ve got this Primary Column for publication in our calendar: =([Event Title]@row + ", kl. " + Start@row + "-" + End@row + ", " + [Contact Name]@row) I’d like to only show this if dropdown column ”Booking” has the value ”Verified”. If dropdown column "Booking" is anything else, I'd like the Primary Column to be empty.…
-
Assistance - Adding a year criteria to SUMIF formula.
The below formula has been working great but I now was to add a criteria that the date column also needs to be in year 2022. I am not sure how best to go about doing this. Any help would be much appreciated. =SUMIF({Sheet - THE Master Range 1}, CONTAINS("E - Quote Accepted", @cell), {Sheet - THE Master Range 2}) +…
-
IF/AND date formula to flag a delayed task
Hi! I want my formula to flag in red when a task is late, that is, when it is still "in progress" when the "end date" is today. The smartsheet accepted the formula below, but it didn't work. Can you help me? =IF(State@row = "complete", "Green", IF(State@row = "in progress", "Yellow", IF(State@row = "recurrent", "Yellow",…
-
#UNPARSEABLE with multiple IF statements
Hi, I am trying to create TEXT depending on some conditional information. I want to display text RED if my materials clear date is less than 7 days from my due date, display text YELLOW if my materials clear date is less than 14 days (but greater than 7 days), and if my materials clear date is greater than 15 days, leave…
-
Countifs formula
Hi, I want count where date column is not blank and country is Australia. Example is below where Smartsheet completion is not blank and country is Australia so need to calculate count of dates. How to write Countifs formula with not blank as criteria. Have tried following formula but it shows error: =countifs([Smartsheet…
