-
Updating a Date Column after another cell is updated.
I am trying to have a Date Column update with today's date when a single select option is chosen. I don't want to use the system update date column because that will get updated whenever any cell is selected. For example: I have a single selection column with several options: Estimating Complete, Quote Sent, and Sale. I…
-
Use Wildcards in an INDEX MATCH formula
Hi everyone, I need to use INDEX MATCH formula to get a DS# with a CNDT# in another report. The easy formula would be: =INDEX({DS Report Range 2}, MATCH([CNDT Number]1, {DS Report Range 3}, 0)), "") but the thing is that sometimes there are more than one CNDT#'s in a single cell. Since INDEX MATCH looks for the exact match…
-
RYGB Symbols Average Formula
I currently having issues with creating formula that gets the average of 4 cells that have the option of 4 different symbols (Red,Yellow,Green,Blue). The goal is to show the average of the four cell symbol's in one here is what I have so far. =IF(COUNTIF([PW Goal]3, [BB Goal]3, [JQ Goal]3, [MC Goal]3, "Red")) = 4, "Red",…
-
VLOOKUP Multiple Matches
I have a table that sometimes has duplicate entries in a column. I'm doing a VLOOKUP on this column, however, obviously, if there is more than one match, this generates an error. What I'd like to do is return a match for the most recent duplicate entry, or in the case of a sheet that is filled via a form, the item farther…
-
New Columns Not Available to Select in Report Builder
I am using the Communications template Smartsheet has available. I modified the categories to suit them to our business. I went into Report Builder to modify the report and the categories do not appear to select. Just for testing's sake, I changed all the categories back to the original names and report ran and calculated…
-
How can I count the amount of actions in one specific month?
Hi, I'm trying to set up a counter for a number of actions in the month of may, for example if I have 5 actions this month (May), I want the counter to return "5". I've been trying to use the formula below but all I get in return is a zero: =COUNTIFS([Target Due Date]:[Target Due Date], IFERROR(MONTH([Target Due Date]1),…
-
Counting amount of actions in one month
Hi, I'm trying to set up a counter for a number of actions in the month of may, for example if I have 5 actions this month, I want the counter to return "5". I've been trying to use the formula below but all I get in return is a zero: =COUNTIFS([Target Due Date]:[Target Due Date], IFERROR(MONTH([Target Due Date]1), 0) = 5)
-
Formula to reference one column based on entries from another column.
I need help on a formula for Project Assignment when a dollar amount is what determines who gets a Project assignment. For example, A Facilities coordinator will get a project if it falls between $5k - $24,999 Project Manager A will get a project if it falls between $25k and $99,999 Project Manager B will get a project if…
-
What is the issue with my formula?
=IF(AND(% Complete]3 < 1,[End Date]3 < TODAY()), "On Time", IF(AND([% Complete]3 < 1,[End Date]3 = TODAY()), "Due", IF(AND([% Complete]3 < 1,[End Date]3 > TODAY()), "Past Due","Complete"))) If i validate each part of the formula separately it returns my intended value however when I try to embed using IF AND, it is always…
-
COUNTIFS criteria including dates after X
I'm trying to create a formula that counts the number of entries submitted to a separate sheet per person completed since a specific date (3/24/2020). My formula that works for counting all entries by individual is =COUNTIF({Other Sheet Range1}, [Member Name]1) but when I try adding the AND {Other Sheet Range2}…