-
Countif query
Hi Guys trying to work out a formula that will allow me to count the total number within a range based on a criteria from another column in the same sheet. If my FY20 Column has Green Yes symbol in it I want to be able to then count to total quotes I have in my quotes value column to show status against the yes in the FY20…
-
Flag for past due and complete
Hi, I am new to Smartsheet formulas. I am trying to initiate a red flag when an item is past the due date (‘due date’ column) and the ‘complete’ column checkbox is unchecked; and, green flag when ‘complete’ checkbox is checked; regardless of the date condition. I have tried several different formulas found…
-
How do I create a cross-sheet formula to pull with multiple date dependancies?
I am trying to find a function that would pull a value from one sheet to another that is dependent on different effective dates. Basically, I have a sheet with all employees' wages and effective dates. New effective dates show up on different rows. I have several different sheets that show each grant my organization is…
-
Vlookup to another sheet and the use of @cell / @row
Hi, I'm using a Vlookup formula (=VLOOKUP(true, {ABC Range 3}, 3, false)) to get back information from another sheet (ABC) only when a row is flagged. When dragging the current formula I only manage to get the value from the first row that is flagged. I guess that @row or @cell could help me solving my problem but I didn't…
-
Nested IF with Multiple criteria
I am working on an approval by facility. The first iteration had one approver list, this is working great. =IF([Contract Expense Variance]1 < 5001, (VLOOKUP("5K", {Voting List Levels Range 6}, 2, false)), IF(AND([Contract Expense Variance]1 > 5000, [Contract Expense Variance]1 < 15001), (VLOOKUP("15K", {Voting List Levels…
-
How do I mark a row complete based on answers from another cell?
I have a cell called Bucket with 6 possible options(Needs Review, Not Doing, Easy to Do, Should Do Needs Comms, Done) and want the status field to mark complete if the cell is anything but Needs Review I tried =IF([Bucket]1 "Not Doing" = "Complete") just to make one work that was marked as Not Doing as a start but on the…
-
Adding Sentences to Nested IF Formula, based on Impact
Smartsheet Community: I having Issues Adding Verbiage to my Formula: =IF(Impact@row = "Highest", "P1", IF(Impact@row = "High", "P2", IF(Impact@row = "Low", "P3", IF(Impact@row = "Lowest", "P4")))) P1: The issue impedes QA and/or further development until resolved. The code cannot provide the core functionality at all…
-
COUNTIFS for multiple ranges
I am trying to use COUNTIFS to add the values in multiple ranges for items that have a value of PASS. So far I can only pull the value in 1 range and i get "unparseable" if I try to add the values together---any ideas? =("PASS " + "[" + (COUNTIFS(Status12:Status16, "PASS")) + "]")---this works only for 1 range of values
-
IF and CountIFS - Invalid Column value
Hi - Attempting to use IF and CountIFS to bring back info from another referenced sheet. Using this formula I am able to return the specified text: =IF(COUNTIFS({Sheet 2 reference column Range 1}, [Column1]@row, {Sheet 2 column range 2}, [Column2]@row) > 0, "blah") Using this formula I receive the "invalid column value":…
-
How do I use @row in an IF formula when I am referencing many columns for 1 response?
I created a plan with 8 goals, each goal has their own objectives (range from 3-11 objectives each goal, and each objective has their own strategies (range from 3-12 strategies each objective). I want to reference any one of the 42 columns I created for the form logic so drop downs would reflect in the objective or…