-
Cell Changed Color When Formula Updated
I just updated a date cell formula to give someone a little longer to complete a list of tasks and they all turned yellow. It' not a cell filled color because you can't remove it. I have updated this formula before and not had any issues with the box changing colors, so I can't figure out why it's doing it now. It's a…
-
Index/Match with Has and Specific Select
I have an master roster and each week I need to ensure a response for each. Since each responder has mutliple areas of responsibility, one submission may cover multiple items from the master roster. I used an index/match formula however when there are multiple entries in a single line, I get a #No Match. And now that the…
-
Compare Contact Column vs Email contact Column
I am using the proof info columns, and sadly they only return the contact email. I want to compare this to a "Reviewer #1" contact column to see if everyone in the Reviewer #1 column has approved, I tried the following formula: =IF(CONTAINS([Reviewer #1]@row, [Approved By]@row), 1, 0) and it did not work. Any thoughts?…
-
RAID Risk Matrix
Hi, I have the below matrix, is there any easy way to get the formula to do the various risk levels (low, medium, high and extreme)? I have attempted to try with AI the formula but for some reason starting with the last scenario of Extreme it stopped generating a formula.
-
Using Match to look for multiple strings and how to evaluate a #NO MATCH
Ok, I am trying to find a way to use Match to look for two values in a column and return the first one found. I know you cannot look for multiple strings in the same formula (at least I haven't been able to write one), so I created two helper columns. The first helper column looks for string A and the second helper column…
-
Index/Collect with Has while meeting specific criteria
Good afternoon community, I have two sheets where the first (we'll call this one Inventory Math) has a formula written to look at the second sheet (we'll call this one Inventory Cost). In this formula, we are looking for specific criteria on the cost sheet and then subtract from a specific cell on the math sheet. The…
-
Trying to capture past dates and future dates
So, I have a column where I can predicted dates (to pay an invoice). Some of these values are past the predicted date of payment. Therefore, I want to make a formula that will capture all those whose date is in the past and those invoices whose predicted pay day is in the next 30 days. So far, I am only able to do :…
-
Why would the absolute reference change after sorting on a column?
I am creating a weighted score card. All my formulas appear to work as expected, with the exception of the Rank formula. Whenever I sort the sheet by either Team Member or Manager (both are setup as contact lists) the Rank function returns a #NO MATCH error for some of the rows. Prior to sorting, the RANK formula is:…
-
Unix/Epoch Timestamp to DATETIME format?
I'm trying to convert a Epoch Timestamp into a DATETIME format. I've had success using formulas getting it into a date, but am unable to get the time. Anyone have any insight on how to accomplish this? 1517577336206 as a timestamp example which I'd like to convert to 2/2/2018 8:15:36
-
Formula or Automation Help
Im looking for the best way to set up this formula/automation (whichever is best): I have multiple columns collecting different contact names by title from a form: I need a formula or automation, that when "Admin Reviewer" = yes, then it takes the contact in "Project Admin" and adds it to another column titled "PT Proof…
