-
Nested IF (AND (OR))) Formula for Flagging
Hi there, I want to be able to flag a column based on a "Status" and not by using a "% Complete" column. I want to flag a row/task that is <= 3 days from an "End Date" column value and IF that "Status" if "Not Started" OR "N/A" OR "In Progress. The formula I've come up with is : =IF(AND([End Date]@row < TODAY(3),…
-
Matching values from different datasets
Having difficulty trying to match 2 distinct values from one sheet to another. We have one dataset that tracks submissions on a daily basis from multiple locations. I been trying to find a way to match 2 distinct values from one sheet to cross reference the master sheet to verify if a form submission was conducted from the…
-
Sum up rows within the VLOOKUP formula
Hi everyone! I have several projects in my time tracking sheet and would like to use the VLOOKUP formula to save only the times per project in a monthly cost table. For that I used the following formula: =VLOOKUP([Project Number]12, {Sheet - Time Tracker (10-October) Range 1}, 8, false). Unfortunately, I only got one labor…
-
Extract a number from the Left of a cell - HELP!
Good Day to ALL. I am new to the vommunity and need some help.... I have a survey form for my clients and when the results are populate, I would like to tally the results - by client. What I am looking to do is extract the number to the left of the survey response. eg. 5 - Very Satisfied 4 - Somewhat satisfied 3 - Neutral…
-
Sum column if date is blank
I'm running into issues summing contracted fees for projects that are unscheduled. I have formulas set to sum fees per month already but unable to find a formula that works to sum fees based on no date entered. The formula outputs are in a summary being pulled from the active project sheet. The month formula for Dec 2019:…
-
Nested IF Statement
I'm fairly new to Smartsheet and I'm trying to calculate a planned budget based on an "Assigned to" column. I'm getting unparseable, so I'm missing something. Here is what I'm trying to build if someone might help me solve the issue... =IF([Assigned To]@row = "Bob Dole", [Planned Hr]@row * 175), IF([Assigned To]@row = "Abe…
-
Auto populate Percentage Complete
Hi, I have a "task" in my sheet which lasts for 3 months. Is there a way to autopopulate the percentage complete based purely on the time passed? It is not a measure of outputs / delivery but just showing how long an external resource is in place. For example, after 1 month, it would say 30% complete. Many thanks, Siobhan
-
Date Range Formula
Hello, I am having an issue when trying to return values like "FY22" based on a date range. What I need is that is the date is between FY 06/01/2021 and 05/31/2022 for it to return FY22, if not then x. I get #Invalid Operation, and I've tried many different ways. This is what I have now: =IF(AND([FY Eligible Date]@row >=…
-
Within an IF Formula, recognizing a date prior to.
Hello. I have created a formula to indicate the status of our project based on today's date and if it has met the date each phase is completed. =IF(TODAY() > Date82, "Operational", IF(TODAY() > Date77, "Pre-Operational", IF(TODAY() > Date72, "Under Construction", "In Development"))) The phases were put in reverse, to…
-
Connecting sheets
I have a large smartsheet project management based, it has a lot of data in it. I wanted to make another sheet to use as a budget tracking sheet but I want the costs per item to automatically upload from the original document. I have tried Vlookups, index with match, I am pretty lost in this search. I just want to layout a…