-
Complex nested IF, AND with traffic light symbols
Hi All, I'm working on automating a symbol highlighter to indicate project progress as well as alerting if key milestones are being compromised. The formula below is structured based on the following columns: * Finish: The Activity completion date * Start: The activity start date * Date: A column containing fixed dates to…
-
Locking Columns/Drop Down Lists
I need to be able to protect formulas in a column for the parent rows but still want people to be able be able to select child drop down cells. If I lock the column will this stop people from being able to use the drop down lists? If so what other options do I have to protect the parent rows' formulas?
-
Multiple Functions per Cell/Column
Hi, Is it possible for a single Cell/Column to have multiple functions and if so, how do you achieve it? For reference, I am trying to track the amount of days between different date columns, however it might take a few hours/days to input the second date. Because of this the cell is filled with #Invalad Data Type. Is…
-
Need to Count Projects by Assignee AND Status
I am having difficulty getting a project count by Person AND has to be one of 3 Statuses. Currently I have this formula to look at project count by a certain person but its pulling in all projects even DONE or Archived. NOTE: The assignee can be more than one person =COUNTIF(GDC:GDC, FIND("first last", @cell) > 0) All I…
-
IF AND Formula for 5 Options
Hey Community, please help me figure this out. We have 5 options for types of projects and they are the following: Technical Services Projects Technical Services Programs Enterprise Projects Enterprise Programs ODE We are trying to output one of those five options based on the columns Project Type and Program Type. Project…
-
INDEX-COLLECT formula returning #INVALIDVALUE error
I'm trying to figure out a way to find the latest completed task in a Gantt. I'm using the formula below - =INDEX(COLLECT([Task Name]:[Task Name], Finish:Finish, MAX(Finish:Finish), Status:Status, "Complete"), 1) but I'm getting an invalid value error. Thanks in advance!
-
Email to Name Automation/function
Hi, Is there a way to change an email, entered into a cell, to the name of the person? I figured that I could created an address sheet and use a cross-sheet, matching/index, formula. However I am hoping that there is a more simple and less time-consuming way. Any information would be helpful, thank you!
-
Adding a condition to recognize if the external reference cell has any data or no data at all
I have a metrics sheet that pulls data from another sheet. I want the formula to mirror the way the Filter feature works to say "if this cell has any data whatsoever in it (in this case any name) then count it. if the cell has no data, then don't count it." This is how I have started my formula: =COUNTIFS({Post Closing…
-
Choose Older Date between 2 dates and then calculate # of Years
Hi everyone! I have a list that shows hire date and in some instances a rehire date. I want to be able to calculate the years of service based on which ever date is the newest. See snip. This is the formula and it calcs correctly on the hire date, but returns blank if there is a rehire date. What am I missing?? =IF([Hire…
-
How to create a SUMIF with ISNUMBER
I am trying to add three separate columns together, but sometimes, one column will have text in it instead of a number. Is there a way to get the formula to exclude anything that's not a number? My brain isn't functioning high enough to come up with something that works lol. This is my current formula: =IFERROR(Rate@row +…