-
Retrieve data from another sheet and add to bottom of destination sheet
I am using the formula below to insert a row into a Scorecard sheet to show the {AP Project ID} from the Approved Projects sheet when the {AP Release Cycle} = 2024 and the {AP Project Status} = “Complete”. I created a helper field called row ID. =IFERROR(INDEX(COLLECT({AP Project ID}, {AP Release Cycle}, “2024”, {AP…
-
Using Match to look for multiple strings and how to evaluate a #NO MATCH
Ok, I am trying to find a way to use Match to look for two values in a column and return the first one found. I know you cannot look for multiple strings in the same formula (at least I haven't been able to write one), so I created two helper columns. The first helper column looks for string A and the second helper column…
-
Can i copy a row one time when criteria is met or will this happen constantly?
Hi im trying to automate my works sheet so that when criteria is met, a row is copied to another sheet automatically… my concern is that when that criteria is met, i will get numerous duplicate copies- is that correct? Is there a work around?
-
Determining Status by Percentage
I'm trying to determine a risk status based on a percentage. If 59% or less, High Risk. If 60-79%, Off Track. If 80% or higher, On Track. My formula is =IF(Percentage1 > 80, "On Track", IF(Percentage1 >= 60, "Off Track", "High Risk")), which is coming up as High Risk and it should be Off Track. I tried putting this through…
-
Cell won't return to blank when values are deleted
So I have this issue where even though the formula instructs the cell to blank, however when after I delete the probability and impact numbers the cell in the Severity field will not return to blank but reverts to Low. Description of the formula: - Blank if probability or impact level is empty. - "Low" if the product is…
-
Best way to dynamically represent a second critical path
I have a plan with a critical path which works fine for the latest date in the sheet, however, I'd like to be able to dynamically see a critical path for another /earlier milestone. Has anyone come up with any way to do that? A workaround I'm doing is a "Save As" and deleting those laster items out but that's manual and…
-
Index/Collect with Has while meeting specific criteria
Good afternoon community, I have two sheets where the first (we'll call this one Inventory Math) has a formula written to look at the second sheet (we'll call this one Inventory Cost). In this formula, we are looking for specific criteria on the cost sheet and then subtract from a specific cell on the math sheet. The…
-
Trying to capture past dates and future dates
So, I have a column where I can predicted dates (to pay an invoice). Some of these values are past the predicted date of payment. Therefore, I want to make a formula that will capture all those whose date is in the past and those invoices whose predicted pay day is in the next 30 days. So far, I am only able to do :…
-
Formula Help: Switching Middle and Last Names in Text String
This discussion was created from comments split from: How to convert Full name to Last name, First and middle Name(s)?.
-
Index Match with multiple results?
We are tracking year end evaluations submitted by the supervisors for their staff, but when I use Index/Match, the only value returned is the first staff member reviewed by that supervisor. Is there a way to find and combine all results by supervisor? Thanks! Kristin
