-
Nested If/And but need and And/Or
I am trying to have the Opportunity Status ball automatically turn green when the four columns to the right are Green OR Gray. I can get the formula to work with just "Green", but when I try to put the OR "Gray" into the formula, it results in a yellow ball or unparseable. =IF(AND([Commercial Business Case Completed]1 =…
-
How to pick the latest date from Table 1 and enter it to the cell of Table 2?
Table 1 is a list of products that contains among others columns Trade Name, Country, Crop and Last Modified (Auto). Within the rows, one trade name can refer to different countries or crops. In Table 2 there are only Trade Name and Date columns. I need to choose the latest date from the rows of Table 1 referred to the…
-
Help: #INCORRECT ARGUMENT SET
Trying to figure why this isn't working...the first two formulas seems to work but adding the third gives me the #INCORRECT error...Please let me know what I did wrong? =IF(ISBLANK([1st PRODUCT_OWNER APPROVAL]@row), "", IF(ISBLANK([2nd PRODUCT OWNER APPROVAL]@row), "", IF(AND([1st PRODUCT_OWNER APPROVAL]@row = "APPROVED",…
-
Terminating a formula once criteria met
Hi! I am working on a sheet that has a basic if formula to populate the Red, Yellow, Green symbols. I want to be able to have the cell not change or have the formula run once the line is shown as "Red." My formula is =IF(AND([Scheduled End Date]1 < TODAY(), Status1 <> "Closed"), "Red", IF([Scheduled End Date]1 <> [Actual…
-
How can I use the SUM function in a column, but only add/account for numbers that are in child rows?
Is there a formula that I could use to SUM numbers in a column, but NOT accounting for values that are in parent rows? The formula would go on a sheet summary; the reason not to account for values in parent rows, is to avoid duplication, since they are a reflection of the information in their child rows. Thank you!
-
FIRST OF THE MONTH AFTER 60 DAYS
I am trying to auto populate effective dates for benefits and I cannot get the calculation to work past the first part (60 days after start date) I need to have the following result as an example Start date: 1/15/2020 Effective Date: 4/1/2020 Thanks!
-
Help with INDEX MATCH COLLECT formula
Hi all, I'm having trouble with a lookup-type function returning the wrong values. We have our base sheet called the "Deal Tracker" where we keep tabs on the various deals that we consider (we're an investment firm). When entering a new deal, we always collect a Contact Name of the person who brought us the deal. We often…
-
Countifs across multiple columns
Hi I want to count how many times Name=xx, has Green/Yellow/red balls across 7 columns =COUNTIFS({C-test Creation House applications Range 5}; [Column4]13; {C-test Creation House applications Range 6}; [Column5]12) (the range 5 is the reviewer column, and the range 6 are the 7 columns) It works if I only choose 1 column,…
-
Traffic Light Formula Based on Date and % Complete
Hello, I am trying to create a health status formula using the traffic light: Red, Yellow, Green Blue symbols. I would like the status to update based on the Deadline as well as the % Complete. The second set of conditions within the formula will not return a result. Here is the formula: =IF([% Complete]@row = 1, "Blue",…
-
Comparisons of dates(Committed date,New Committed date & Actual Completion Date)
Hello I have 3 dates column in smart sheet i.e Committed date,New Committed date & Actual Completion Date I need to compare these dates like if Commited date is> New committed date then i should get some color & the same way if committed date>actual completion date then i should get color & the same if New committed date…