-
Need better solution for multiple nested IF statement
Hello, I have a sheet that tracks sprint numbers based on their date ranges. How can I identify the sprint number for a task using the task start date? Right now I'm using this very long formula in the Return sprint number column, and I'm hoping there is a better way to do this. Thank you! =IF(AND([Task Start Date]@row >=…
-
Formula Help: Show Status looking left to right, of what milestone cells show
Have a HUGE team smartsheet that several departments update other sheets and feed to this one. Want to have a status column based on the condition of milestone cells (will skip some minor ones) to show where we are as a team. So only one status should show. Thanks in advance! This is what I have so far: =IF([Date…
-
Nested IF Fomula help
Hi! Can anyone tell me what is wrong with my formula below? I am having trouble identifying =IF(ISBLANK([Contractor Name]@row), "No", IF(OR([Contractor Name]@row = "NA"), "No", IF([Contractor Name]@row = "RLM"), "No", IF([Contractor Name]@row = "Danny"), "No", IF(ISBLANK(Phone@row), "No", "Yes")))
-
IF OR/AND Function - Considering Dates (Future, Past etc)
Hi Team - I have a bit of a doozy.. at least it is for me. I have a sheet that needs a status populated considering 2 different date columns. If either "Expiry Date" or "Expiry Date 2" is in the past we need a "Red" status If either "Expiry Date" or "Expiry Date 2" columns are within the next 30 days "Yellow" Status If…
-
IF ERROR help for a nested IF AVG formula
Hi there! I have an IF followed by AVG formula that is working ok- looks at multiple columns and averages them (frankly it is problematic, because it returns an average even when not all 6 columns are filled out, help? Is there a way for it to only calculate the average once all 6 columns have a value added? Honestly if…
-
Will IF(MAX(Collect work?
Hello community, I am using a MAX(Collect( formula to pull in a number from another sheet where I calculate the number of days we have to lock a project's files after completion - this is pulling into our project tracker. The origin sheet is calculating the countdown number based on one particular type of work request and…
-
How to use if contains with similar words
I want to use if contains to find a word and classify it The problem I have is they are to similar it will always Classify it as A and not B. the description is to long for Has or just a regular if Statement. =IF(CONTAINS("EOR", description@row), "A", IF(CONTAINS("BOR", description@row), "A", IF(CONTAINS("CBOR",…
-
Need a formula to fill in a column based on a date column
Hi! I have a date column and i would like a forumla in another column to complete that cell with the word "completed" when the date is added. Im creating an exterior report that i want to show just the info for the completed rows but the only way we know its completed is by this date being added. If the date column is…
-
If Cell Blank Return "N/A"
If Date of Calibration is blank, return N/A instead of DUE. =IF([Calibration Due]@row - TODAY() > 90, "GOOD", IF([Calibration Due]@row - TODAY() > 30, "3 MONTHS", IF([Calibration Due]@row - TODAY() > 0, "1 MONTH", "DUE")))
-
Formula Question-I need to create a IF formula based on other cells criteria
I need this formula in the June 2024 column and below is what I need it to do. It should be one big long formula and I'm assuming it would be a IF statement. I could set up automations for this but that's a lot more work than just figuring out a formula since I am going to use this formula for many months to come on this…
