-
Help with nested If formulat
These formulas work independently. I am attempting to combine them into one nested if formula. 1) if the %complete is full, status is green: =IF([% Complete]10 = "Full", "Green" , "Red") 2) if the finish date is greater thant today, status is green: =IF(Finish10 > TODAY(), "Green") 3) if %complete is full and finish date…
-
VLOOKUP and Conditional Formatting
I updated our smartsheet team schedule to grab dates from my department's master schedule. However, the conditional formatting we set up that visually shows us when a project is nearing or past its due date no longer works on the VLOOKUP cells Is there something I'm doing wrong? Or is this a limitation of the software? Has…
-
% as Negative
How do you structure your formula calculate negative if it is over 100% For example, I am at 144% which is over budget Current Formula =AC1 / [Project Budget]1 Results 144%, I would like it to calculate -44% once I am over budget.
-
Nesting SUBSTITUTE functions
Hello All, I need help!!! I am trying to write a formula that replaces the letter "a" with the number "1", "b" with "2", "c" with "3", etc.... The individual formula of (ex.) =SUBSTITUTE([Column3]1, "a", "1") is easy enough, but for whatever reason I can't seem to figure out how to nest it with the rest of the letters and…
-
Counting Unique Values based on a date criteria
I am using this formula to count for unique values within a range of cell. I want to be able to use a criteria, a date for the range of cell, what should the formula look like? =SUM((IF(FREQUENCY(MATCH(B2:B16,B2:B16,0),MATCH(B2:B16,B2:B16,0))>0,1)))
-
Help on formula: How to copy adjacent text using a specific text from a different column
Hi all, Please need help on formula. The example image below is a project with sub tasks and I wanted to create a formula in the Progress column highlighted in yellow (this is the main header). The condition is: if the status of the row is "Active", then show me what is its current Progress. Based on the screenshot below,…
-
Adding %Complete back to smartsheet
I accidentally deleted the %complete column in my smartsheet. How do I insert it back into the project? I don't see a quick way of doing this.
-
Auto Fill Date every 30 Days
Good Day All: Is there a way to Auto-fill dates? For example: 04/30/18 05/31/18 06/29/18 Thanks
-
Calculating a date from another date in same row
how do I have the start prepping date calculate off the meeting date automatically in the same row. ie I have a meeting on 1/22/18 and I need to start planning 10 weeks prior and would like it to be automated. The problem is this is a major calendar for over 30 meetings, and all have different lag times for planning. I…
-
Report with contact details
Hi, I want to create report with contact details based on data from my sheet. The problem I have is that in sheet I have some duplicates. Is there a way to add only one of duplicates to the report?