-
Formula with Dates
I am trying to reference another sheet where that column will be marked "completed" and then using the star symbol, have it toggled on if the date is in the month of January. Any help is appreciated! I'm new at this :) =IF({RANGE 1}, "Completed", IF(AND({RANGE 2}, <=DATE(2022, 1, 31), {RANGE 2}, >=DATE(2022, 1, 1), 1, 0)))
-
Automatic Data Dragging
Hi Community, I am looking for a formula that can help me to pull data from one smartsheet to another. First thing that came in my mind was the VLOOKUP, but in this case it is not sufficient. The formula should pull the all the rows (complete row) from another smartsheet, but only if the budget impact (multiple columns) is…
-
Formula to count In Progress
Hi, can anyone help with this formula? I need to count the children in a smart sheet project and not include the parent or grandparent as they have formulas that alter based on the overall % of the project as a whole. I have helper columns that indicate level, and I need this to ignore the summary count from 1,2,3 etc...…
-
Formula to return 'TBA' if cell contains text 'TBA'
Hi, I'm currently using this formula which works: =IF([\[H\] Variance (%)]@row < 0, "Under Budget", IF([\[H\] Variance (%)]@row = 0, "On Budget", IF(AND([\[H\] Variance (%)]@row > 0, [\[H\] Variance (%)]@row < 0.15), "Moderate Budget Overrun", IF([\[H\] Variance (%)]@row > 0.15, "Significant Budget Overrun")))) However, I…
-
Symbol Formula using date and another field
I need to create a formula based on the following criteria: 1) Contract Type is...Lease....then the following needs to be assessed in the Target Completion Date column. 2) Target Completion Date is more than 70 days out "Green" 3) Target Completion Date is 20 days out or less "Yellow" 4) Target Completion Date is in the…
-
How does Collect function apply in this case?
For the sake of data integrity in a big sheet that has a column for a cost name and its amount, I need the same values to be populated for the same cost name. for instance: if one column has items names and the second has values: Item1 20 Item2 30 Item3 40 Item 1 (I need to have here 20 in this cell) Item3 (I need to have…
-
Newbie Question: Want to Count "Product Name" but only if its date is in the future
Hello! I am very new to Smartsheets and I am trying to get the best use out of it. But, I am little stumped. I have a COUNTIF formula to count how many times a product name is mentioned in the sheet and then I used conditional formatting to highlight it if there is more than one time the product is mentioned.…
-
Different dates for Status RYGG
Hi! I have been pulling my hair out over this. I have gotten each part to work independently, but not together. We are trying to get the status to change if two separate dates are past due, or 30 days from coming due. We also have a column that would select the gray status if it is selected. Below is what I had written.…
-
How to use AVG and COLLECT to average data based on multiple criteria?
Goal: To average the estimated GP data in a column on a reference sheet based on the person assigned to the projects and the completion date being within a specific quarter. For example, I need to know the average estimated GP for all projects assigned to PersonXYZ, for March, April, and May. I have the Average working for…
-
How do I display a different symbol based on multiple values?
I've searched the community and tried multiple formulas but I cannot seem to get this scenario to work. I have a '% Not Achieved' summary field, and depending on the % I want Green, Yellow or Red to display. Scenario: 0% = Green 1% - 10% = Yellow 11% and over = Red This is the formula I'm currently using. I get the Green…