-
Can you auto populate task dates based on the entire project end date?
I have different marketing tasks where their completion dates revolve around an entire projects completion date. For example, there are tasks that need to be completed 10 days before project completion date, 20 days prior, 30 days prior, etc. Is there a formula I can use that auto fills task due dates based on the project…
-
Complex formulas ammendment
Hi All, I have an existing Smartsheet that has some relatively complex formulas to count the the status of an action as there are several conditions for an action to show as "Open" and "Overdue". Over the last few days, we have modified the sheet further by adding columns and each added column now comes under as added…
-
Nested IF AND OR
I am trying to get a formula for a check box that combines an if AND with an If OR. I have a column labeled Rental Billed that is a check box. There is another column that has multiple status options from a drop down. I want the formula to return a 1,0 if the rental billed column @row is not checked AND the Status is set…
-
COUNTIF Help
Hello, Is there a way to count the number of times a contact appears in a certain column, if there are multiple contacts in some of the cells? For example, I can use COUNTIF to see how many times Charles appears in cells where it is just his contact only, but I want to count how many times Charles appears even if there is…
-
The 25,000 cell reference limit is puzzling
Dear Smartsheet: I am a bit confused by the 25,000 cell reference limit. In my specific case I am simply trying to VLOOKUP from a table where I have 700 rows and 17 columns. That equals 11,900 cells which is less than 25,000 cells. So, why am I getting this error? It creates an incredible amount of frustration and…
-
Multiple IF/AND for duration and status
Hi Community, I am trying to structure an IF/AND formula to reflect the following conditions: IF [Duration]@row <> 0, AND IF [Complete]@row =0, "Not Started" IF [Duration]@row <> 0, AND IF [Complete]@row =0, AND IF[Complete]@ previous row = 1, "In Progress" IF [Duration]@row <> 0, AND IF [Complete]@row =1, "Complete" IF…
-
Parent cell - Status symbol total
Hi there, How can the parent cell (circled in blue) automatically reflect the correct color/symbol concerning the tasks below? When I say automatically, I refer to do it by default, not me selecting the symbol all the time. Can this be done with a formula, and if so, which formula? Thanks in advance M
-
COUNTIFS Formula
I am fairly new to working with formulas. I have a Project Status column that contains several values with "In Progress". For example, In Progress - Scoping, In Progress - Development, etc. I would like to count all the instances of any variation of In Progress. I have tried working with HAS and CONTAINS and recently came…
-
How to update a formula to automatically populate for the prior Monday to a cell date
Hi! I have the following formula from the discussion board (shoutout to Craig) for automatically populating the Monday prior to a certain date: =IF(ISDATE(Date@row), IF(WEEKDAY(Date@row) = 1, Date@row - 6, Date@row - WEEKDAY(Date@row) + 2)) What do I need to change on this to have it calculate the FRIDAY prior? I'm sure…
-
IF & AVG functions
I'm looking to create a report of averages and actuals to help predict spending. I'd like to create a function that does the following: Displays the average spend for YTD if today's date is less than the 1st of current month Displays the actual spend for the month if today's date is greater than the 1st I'm starting with,…
