-
Join Collect Header?
I am trying to figure out how to consolidate a group of companies that use a specific application. Each application has its own row. Each company has its own column and a "Y" is added to the field where they intersect to indicate that the application is used by the company. I would like to combine a list of all of the…
-
Formula if multiple choices
I have a Countifs formula that I can get to work if a single item is selected from the dropdown but not if both options are selected. I want to count: 1) Who's affected is Internal (works): =COUNTIFS({ProjectStatus}, "Open", {WhoAffected}, "Internal", {Live}, "Live Input") 2) Who's affected is External (works):…
-
INDEX/MATCH for Mirroring Status Column in Another Sheet
Hello! This may not be right formula, but if a user shared a sheet with me which contains a project codes column and a status column (complete/incomplete), and I use the same project codes, can I do a formula which pulls in their status into my sheet? Something like - If (External Referenced Sheet / Column A) matches…
-
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…