-
IF & VLOOKUP Formula Only Returning First Value Found
Hello, everyone. I'm having a problem where I'd like the formula in [Column 3] to report the value in [Primary Column] only if the checkbox in [Column 2] is TRUE (checked). (In the real sheets, the VLOOKUP formula in [Column3] is on another sheet from the values in [Primary Column]:[Column2] here, otherwise I'm sure it's…
-
Creating a dropdown list from values in a column on another sheet
I am trying to create a simple dropdown list of values to select from a customized list of values in another sheet. This list will change over time, so I want to use this reference sheet to update the list values. I have setup the list in Sheet Reference Manager and there is only one column of data in the reference sheet…
-
conditional formatting not working
I'm trying to make my conditional formatting work so that a column is highlighted a specific color is the % Completed column is over a certain amount and the Due Date is in the next 7 days. I even moved this rule close to the top of the list, just to make sure this would work. Can anyone help me fix this?
-
Subtracting months from a Date
Hello, I found the below formula that works to subtract months from a date field, however it only works when its going into another year. If its within the same year it does not work and receive an Incorrect Argument Error. =IF((MONTH([Day 1 Date (manually add)]@row) - 1) < 1, DATE(YEAR([Day 1 Date (manually add)]@row) -…
-
Stuck on a formula
Hello, I'm trying to create a formula that essentially takes a predetermined number and subtracts 2 sets of other numbers (both a sum of column values and then also subtracting a cell number minus a %). Wow, I've just confused myself. Essentially I'm trying to: =19.2 - SUM([Column23]16-48%) - SUM([Column23]6:[Column23]15)…
-
Help with multiple criteria within a date range
Hi I previously received help with a formula to count the number of a specific activity 'last month', which was then adjusted to take account of the change into a new year. The formula I am using is: =COUNTIFS({Product Specialists Activity Range 4}, @cell = "Courtesy Calls - 1 hour", {Product Specialists Activity Range 5},…
-
Another Nested IF Formula
Could use your help again. Not sure why I have so much trouble with these nested IF statements but I do! When I use the formula below and the Business Unit is equal to EMEA, it returns ASD....any ideas? =IF([Business Unit]@row = "Biologics", "ASD", IF([Business Unit]@row = "Western", "ASD", IF([Business Unit]@row = "Simple…
-
CREATING A SUM RANGE FROM DROPDOWN LIST
Hello everyone, I am currently creating a dynamic sheet wherein it sums a certain range using a dropdown, but i cant figure how i could use the index and match formula. LINK:https://app.smartsheet.com/b/publish?EQBCT=16d13980574b4a76ac8a3378452dc61d The month1 and Month2 would be my dropdown list then it populates the…
-
Formula for a checkbox
Ok so what I am trying to do is put a formula in the background of a checkbox. The purpose is the have the formula check a name in a cell, and then compare that value with a list on another sheet. At the moment I have that list typed one name per cell vertically however I would prefer to be able to put the list into a…
-
"Not Equal To" Help
I have the below formula that works with the exception of the OR function. It is not omitting blank cells or cells that contain "Vacant" in the Emp No in its count. =COUNTIFS({Route}, $[Primary Column]@row, {Landside Storm helper}, 0, {Operations Report Range 1}, <>1, {Day/Night}, [Storm Type Contact]@row, {Emp No},…