-
INDEX/MATCH showing #NOMATCH
I have 2 sheets: Source Sheet: KI Tasks Report Target Sheet: MLE Access Management All three columns in each sheet are text Formula in MLE Access ProjCode column: =INDEX({CE+RxE From KI_ProjectCode}, MATCH(ClientID@row, {CE+RxE From KI_ClientID}, 0)) Formula in Project Manager String column: =INDEX({CE+RxE From KI_PMName},…
-
If cell contains any of "apples", "oranges", "pears" in text string then collect
Hi, I have a column where I want to basically extract certain words from it without having to create multi helper columns. If I do a nested IF(OR(contains) statement or an IF(and nested statements it will look for conditions in order but I want to evaluate and return any of the certain words. I can do this with helper…
-
Setting values from form to zero
I have a form for customer feedback with ratings 1-5 and an N/A option. I'd like to apply logic such that the N/A value is set to 0. So I can work with it in other columns with formulae, avg rating, etc.
-
IF AND OR Statements
Hello! The sheet on the back end has multiple checkmarks to select what role the event is for. I am needing to create a formula that will give a Academy name based off of which checkmarks are checked. The column for academy name is a dropdown column. i.e. If support office is checked support office academy is put in the…
-
Getting sheets to talk to one another
I have two different sheets and I need them to automatically populate cell data when data is entered. I have sheet A (my department sheet) that has many columns and rows (many of which won't apply to B) and I have sheet B (another department sheet) that has a lot less columns and rows. I have set-up a column in each sheet…
-
Return latest Update
Hi Guys I have sheet - Incident Recoded, see pic below…. I have formula - =IFERROR(INDEX(COLLECT(CHILDREN([Incident Description]@row); CHILDREN(Modified@row); MAX(CHILDREN(Modified@row))); MATCH("MTC"; CHILDREN([Primary Column]@row); 0)); "") This formula works if on row 2 in the pic the Incidents is "MTC" but if the 2nd…
-
My risk formula for program row with child Projects progression is not working.
I am tryings to create a risk formula for Program that has list of projects as a child. Following are conditions to mark the project as either Green, Yellow, Red or Blue: Blue: - Project has progress (% Complete > 0) but hasn't officially started yet (Start Date is in the future) - Indicates early progress or a potential…
-
Check box if date is equal to or between dates
I want to create a formula to do the following: If the "Created Date" is equal to OR between the "Week Start" and "Week End" dates, I want to check the "Submitted on Time" box. I tried =IF([Created Date]@row, >=[Week Start]@row, <=[Week End]@row, 1, 0) and am getting an INCORRECTARGUMENT error. I'm sure I'm missing…
-
Balls Changing Colors
Hello, Why is the signal status color column populating a color when I have nothing in the value column? How do I stop this? My current formula is =IF(Value@row >= 90, "Green", IF(Value@row <= 84, "Red", IF(Value@row < 90, "Yellow"))) Thanks!!
-
Problem with nested IF statements
I was successfully able to write a formula to fill in the value of the User 2 Action column with the value of User 1 Action if Prod User 2 is not blank: =IF(AND(NOT(ISBLANK([Prod User 2]@row)), NOT(ISBLANK([User 1 Action]@row))), [User 1 Action]@row), "") I would like to add one more level to this where the above works…