DashboardNovice
Member
I'm trying to get my formula right in cell I4, even though I already copied it down. I am using SUMPRODUCT in I4.
For cell I4, ultimately, I'm trying to return the array in yellow (J5:J28), then I want to apply ISNA( ) to that array to test for the presence of 1 or more #N/A errors. Then I4 would return TRUE or FALSE.
I'm sure there is a better way to do this. SUMPRODUCT might not be the answer and I might be looking at this the wrong way. To return the array in yellow in I4, I am selecting for various criteria starting on the row below since the yellow array also starts on the row below.
1. First, for all values in column H (Level 1), I am selecting for all cases where Level 1 has the same value as H4.
2. Second, I am selecting for all cases where the Level No's is equal to G4 + 1 (which equals 3). Since the project code in J4 is level 2, I want to pull all project codes on the next level lower, which is level 3.
3. At this point, I'm thinking that the result of #1 and #2 above will result rows 5:28. So now I want to look at column J and test if there are any #N/A errors present.
If #N/A is present, I would return "Delete" in cell I4, otherwise return "".
I don't know if my thinking is correct after #1 and #2.
So as I copy the formula down, I am basically looking at the current level #, e.g. 3 in if the formula is in I7, then selecting for all project codes on the next level lower even if that means including any #N/As that might be in the way, i.e. level 4 which would be J8:J11, then testing if #N/A's are present or not.
So the last row of the array can vary as I copy the formulas down.
Currently I have all 3 of my SUMPRODUCT arrays going down to the last row, i.e. 38, but I don't want that. For each cell in column I, I am trying to make it so that the last row of the array will end on the last occurrence of the level 1 value in column H. For example, for my formula in I4, the current Project Code in J4 has a level 1 value of A7278 which is shown in H4. So I want my arrays to end on row 28 because that is the last row with A7278.
I know that H10 and H14 have a value in Level 1 even though column J shows #N/A. This is not a type. In reality I have another column adjacent to Project Codes (column J), but I'm not including that here for simplicity.
For cell I4, ultimately, I'm trying to return the array in yellow (J5:J28), then I want to apply ISNA( ) to that array to test for the presence of 1 or more #N/A errors. Then I4 would return TRUE or FALSE.
I'm sure there is a better way to do this. SUMPRODUCT might not be the answer and I might be looking at this the wrong way. To return the array in yellow in I4, I am selecting for various criteria starting on the row below since the yellow array also starts on the row below.
1. First, for all values in column H (Level 1), I am selecting for all cases where Level 1 has the same value as H4.
2. Second, I am selecting for all cases where the Level No's is equal to G4 + 1 (which equals 3). Since the project code in J4 is level 2, I want to pull all project codes on the next level lower, which is level 3.
3. At this point, I'm thinking that the result of #1 and #2 above will result rows 5:28. So now I want to look at column J and test if there are any #N/A errors present.
If #N/A is present, I would return "Delete" in cell I4, otherwise return "".
I don't know if my thinking is correct after #1 and #2.
So as I copy the formula down, I am basically looking at the current level #, e.g. 3 in if the formula is in I7, then selecting for all project codes on the next level lower even if that means including any #N/As that might be in the way, i.e. level 4 which would be J8:J11, then testing if #N/A's are present or not.
So the last row of the array can vary as I copy the formulas down.
Currently I have all 3 of my SUMPRODUCT arrays going down to the last row, i.e. 38, but I don't want that. For each cell in column I, I am trying to make it so that the last row of the array will end on the last occurrence of the level 1 value in column H. For example, for my formula in I4, the current Project Code in J4 has a level 1 value of A7278 which is shown in H4. So I want my arrays to end on row 28 because that is the last row with A7278.
I know that H10 and H14 have a value in Level 1 even though column J shows #N/A. This is not a type. In reality I have another column adjacent to Project Codes (column J), but I'm not including that here for simplicity.
