-
Vlookup first non-blank match
Hi all, I've spent a few hours trying to find the solution and am about to give up. I've seen similar questions but the question/answers don't make sense for my brain today. I have 2 sheets: 1- One Sheet with a list of contracts. Some people have more than one contract, with some services included on one contract and other…
-
Return Multiple Look-up Values in a Contact List Field
We have a reference sheet which has a first column of Client ID followed by columns for Analyst Email and Manager Email (both are Contact List column type). On another sheet we have multiple columns and are trying to populate both the Analyst Email and the Manager Email values into one contact list field based upon the a…
-
Multiple IF/AND Statements Formula
Hi All, I have a field "Annual Hours Saved". I want to create another column with the following logic: If Annual Hours Saved is 0 to 10, then put 1 If Annual Hours Saved is 11 to 20, then put 2 If Annual Hours Saved is 21 to 50, then put 3 If Annual Hours Saved is more than 51, then put 4 Any help would be appreciated!…
-
Requesting help with ""Length of Stay" formula as per Excel
I am receiving the #UNPARSEABLE message for the following formula to reflect length of stay between two entered dates using =IFDATED([MOVE-IN DATE]1,[ASSESSMENT DATE]1,"Y") & " Yrs, " & DATEDIF([MOVE-IN DATE]1,[ASSESSMENT DATE]1,"YM") & " Mnths, " & DATEDIF([MOVE-IN DATE]1,[ASSESSMENT DATE]1,"MD") & " Days" Please assist.
-
COUNTIF CONTAINS HELP
Hello, I am trying to get a count of how many Greenwaste projects are completed on the sheet. Here is my fx: =COUNTIFS([District Site]1:[District Site]219, "Greenwaste", Status1:Status219, ("Completed")) ^^^ is returning a zero right now, it should count at least 3 as completed. Can someone let me know what I am doing…
-
Traffic Lights for Project Plan
I'm in need of setting up traffic lights (red, yellow and green) for my project plans (x5) Under Schedule Health I would like the following reflected: Any task which is overdue by 4 or more days based on their end date and not 100% completed to be RED Any tasks which is overdue by 3 or less days based on their end date and…
-
Formula is not persisting when value is removed from cell
Hello, We are having an issue with cell formulas. The cell formula is erased when we change or delete a value in a cell. Here is the formula we are using: =IF(Status4 = "Complete", "In Progress") The formula should first check the cell status in Row 4, then change the cell status on the row where the connected cell is…
-
Formula Counting Created Date as Following Day When Recorded After 5:30 PM PDT
When creating a metric sheet that evaluates entries in another sheet, I am attempting to write a COUNTIF formula that counts all entries for that day, but any entries recorded after 5:30 PM PDT are counted towards the following day. I created a helper column that converts the system column into just a date, and that fixes…
-
If Statement Returning Different value if a cell has a single digit in it versus a two digit number
See the sheet below. In the SLA Tally column I want it to look at the [Age of Defect] and if it is larger than [P SLA] return that it is "Outside SLA". If you see on Row 4, it works correctly. It is recognizing that age of the defect is 7, and it is above the SLA range of 7, and returning "Outside SLA". However, when the…
-
INDEX/Collect function with two criteria returns #invalid value sometimes...
Hi There, I have a status update sheet that has many columns. The three relevant here are: 1) Date Column = {createdDate} 2) Checkbox = {PSR} 3) free text column = {Overall Commentary} ----- In another sheet I am trying to collect the row with both the latest date and has the checkbox ticked. Here is my formula:…