-
Using INDEX/DISTINCT with OR function spread across multiple ranges
I use the following formula to condense data from source sheet to destination sheet with the "distinct" feature possibly combined with an OR function. Something like =Index(Distinct(OR({FLOWER UID}), Number@row, ({SHAKE UID}), Number@row)) or =Index(Distinct(OR({FLOWER UID}, {SHAKE UID}), Number@row)) Source sheet with two…
-
formulating dates excluding weekends
I have 3 columns with different dates. My goal is to enter 1 date, and have the other 2 dates formulated based off the 1st date. I created a formula to satisfy this need, but I am ending up with some dates falling on weekends, which I cannot have. Is there a way to correct this issue without enabling dependencies and…
-
Alternative to COUNTIF with HAS Formula
I need help, please :) On a support sheet, I have a list of Zip codes and the areas of the city they correspond to. On my main sheet, I have a "Company HQ Address" column, that contains the Zip code, but NOT on its own column, it's listed within the cell containing the full address. When I use "=COUNTIF({Source Sheet Range…
-
IF AND with multiple conditions
I've got a doozie for you all. Luckily, I'm partially there. I want to run a function with the following conditions: If Task = C or GM, and JXDN = US, then return Trademark/Domain General - US If Task = E or ACQ, and JXDN = US, then return Trademark Disputes - US If Task = P, and JXDN = US, then return Trademark…
-
Need formula to pull in names associated with RACI roles.
I have a Project Plan template that I would like my team members to save as new for each client and customize the information. Within the Project Plan, there's a RACI matrix with different roles. Please see below for an example: When a new client is assigned and team members save the project template as new, I'd like to be…
-
Dashboard Metrics - Pulling in Week
Hi everyone - I'm working backwards here but I have a Project Plan that I'd like to derive metrics from in regards to Client Name, Health, Status, and Week. What I'm stuck on is how to capture which week in the Project Plan we're currently in. Is there any sort of automation that I'm not thinking of? I want to show these…
-
get 2nd word from cell if cell not empty, else first 9 letters of the word if there is no space
I have =IF([Source Detail]@row <> "", MID([Source Detail]@row, FIND(" ", [Source Detail]@row), 100)) what it is missing is if there is no 2nd word, then give me 9 letters from left of the word. Above formula works when I have following word in Source Detail : Source Detail : Testing This Above formula doesn't work when I…
-
How to change Column Values into Row Values?
Hi All, I hope you are doing well. I have a question that 'how to change column values into row values?'. Can you please guide me in this regard? Thanks in advance.... Thanks, Suneel
-
Count if text contains certain words in one column and another column is not blank
Hello! I am stumped on a formula. I am trying to count the number of times the task "Full DD" is listed in the task column the cell in the "Number Completed" column is not blank. I have this formula: =COUNTIFS((Task:Task), "Full DD")(AND([Number Completed]:[Number Completed], NOT(ISBLANK(@cell)))) but it keeps coming back…
-
Combining ISBLANK and TODAY formula for RYG Symbol Column
Hi all, I'd like to automate the Health column in my Project Plan but am not sure how to go about doing this. The Health column is a symbol column (red, yellow, green). Ideally, I'd like for it to show the red symbol if the end date is 3 days late, yellow if the end date is 2 days late, green if we're on track, and blank…
