-
Removing ' from cells
Hello, I imported an Excel spreadsheet containing a column of identification numbers that are 6 digits in length. When I tried to set up an index/match formula using the values in that column, I got a "no match" error. After a little research, I discovered there is a single quote at the beginning of each ID number. I've…
-
Need help with an IF Statements with multiple ORs and an AND
Hi. I need to write an IF statement where if the Review column has one of the following: Review Complete, Reviewed - Not applicable, Do not include, Reviewed - Not applicable, send feedback AND the COMM Review column has the following: COMM Review Complete If both the Review Column has one of the 3 options AND the COMM…
-
Index, Match, Collect and/or Vlookup?
Hello, may I get assistance with a formula? Thank you. Sheet 1 ---> data in columns: Course Code, Section, Instructor Sheet 2 ---> column names: Course Code, Section, Instructor (blank) In sheet 2, I want to pull the instructor information from Sheet 1 based on course code and section in sheet 2. Here's what I have but…
-
Check count by project type AND how many started that month
Hi, I'm trying to run a formula that would count how many projects were completed in a specific month and the column named "Tier" had the value of "Tier 3" but I can't seem to adapt the below successfully to include both conditions i.e. number of tier 3s completed in Jan =COUNTIF([Actual End Date]23:[Actual End Date]81,…
-
Using "Flag" in and IF function
Hello! I am looking to create a formula for the RYG column that would work like this: If Status column is "Overdue", then it shows "Yellow" If Status column is "Complete", then it shows "Green" If Status column is "In Progress", then it shows "Green" If Status column is "Not Started", then it shows "Grey" If At Risk column…
-
IFERROR Formula Should Catch INDEX() Returning #INVALID COLUMN NAME
Recently we noticed that when we have a function that looks like this: =IFERROR(INDEX(COLLECT(...), Index), "") If the Index value was 0 the result of INDEX() was an error (understandably) - #INVALID COLUMN NAME, however IFERROR() did not catch this as and error but was also returning #INVALID COLUMN NAME. This makes not…
-
Formula question - count projects by status, category and date
Hi Everyone, I want to count the number of Project IDs where the Project Status = "Complete", the Project Category matches the column header (AV-TV, Software, etc.) and the End Date is greater than 2022,09,30. I can count the number of overall complete projects for the project category without the date, but cannot seem to…
-
Nested IF/INDEX/MATCH Challenge
I am stumped and hoping someone can help. Here is my sheet: In the [Partner Name] column, I have this formula: =IF(OR(BEGEOID@row >= 34, BEGEOID@row <= 94956), INDEX({BEGEOID Partner List A Partner Name}, MATCH(BEGEOID@row, {BEGEOID Partner List A BEGEOID}, 0)), IF(OR(BEGEOID@row >= 94957, BEGEOID@row <= 431490),…
-
How do i create a status column based off of levels and approvals?
I have to create a status column that uses 3 different levels that each require different people to approve. how do i make one big formula for it all. For example level three needs person one to approve, then person two, then person three. then the status can be complete. basically i need if status is level 2 then person…
-
Average based on 1 criteria
I'm sure this is user error but I'm trying to average out compensation based on the job code then I'd like to create either a data mesh or vlookup to fill in the blank compensation with these averages. I've tried having a seperate sheet that has the job codes and then I'm trying to use the averageif formula. However I keep…
