Index/Match and VBA Question

[mcmahobt]

Hello all,

First post here, hopefully it's in the right spot. I have been scouring the forum looking for a solution to this problem, but to no avail so far.

Basically, I have a workbook that visually reports the Fiscal Year status of our product in the field. The Fiscal Years run from FY 11 to FY 30. The product version is set to automatically change based on the value in a separate spreadsheet, and conditionally change color based on said value. This works fine, and is accomplished by a simple IF statement/formula shown below:

=IF('Fielding Plan '!$D11="Upgrade","CXG 1","")

The problem, however, comes next. Taking a portion of the workbook as an example, the status of our product may look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]FY 11
[/TD]
[TD]FY 12[/TD]
[TD]FY 13[/TD]
[TD]FY 14[/TD]
[TD]FY 15[/TD]
[TD]FY 16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CXG 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CXG 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CXG 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CXG 4[/TD]
[/TR]
</tbody>[/TABLE]

This is done to multiple versions of our product, not just CXG. So what I am trying to accomplish, is to pull all the results to the top of the column, in order to save space when printing. I am trying to get the result to look something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]FY 11
[/TD]
[TD]FY 12[/TD]
[TD]FY 13[/TD]
[TD]FY 14[/TD]
[TD]FY 15[/TD]
[TD]FY 16[/TD]
[/TR]
[TR]
[TD]CXG 1[/TD]
[TD]CXG 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CXG 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CXG 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The problem I have run into with using INDEX/MATCH is that they don't carry over conditional formatting, which I need conserved. I'm assuming VBA code is needed for this, but I do not have enough knowledge in that area to be able to apply it. Any help is greatly appreciated!

 

[mcmahobt]

Try this formula in A2:

=IF(ISNUMBER(SMALL(IF('Fielding Plan '!D$11:D$19="Upgrade",ROW(A$2:A$10)-ROW(A$2)+1),ROWS(A$2:A2))),"CXG "&ROWS(A$2:A2),"")

confirmed with Ctrl+Shift+Enter and copied down to A3:A10.

Andrew,

Tried the formula, but to no avail. It's very similar to the method I was trying before; I think you're onto something with making it an IF statement at the beginning. A quick question though concerning the equation: shouldn't ISNUMBER be ISTEXT if there is a text string in the cell, as is with "CXG"?

Again, thanks for your time and effort. Hopefully this will put me on the right track after some more fiddling.

 

[Andrew Poulsom]

No, it should be ISNUMBER. In what way isn't the formula working?

 

[mcmahobt]

Try this formula in A2:

=IF(ISNUMBER(SMALL(IF('Fielding Plan '!D$11:D$19="Upgrade",ROW(A$2:A$10)-ROW(A$2)+1),ROWS(A$2:A2))),"CXG "&ROWS(A$2:A2),"")

confirmed with Ctrl+Shift+Enter and copied down to A3:A10.

Andrew,

Tried typing in the equation this morning just to humor myself, and it seems to work fine now. I'm not sure what the error was before, but it was obviously on my end. Thanks again for the help!

 

[mcmahobt]

Andrew,

Tried typing in the equation this morning just to humor myself, and it seems to work fine now. I'm not sure what the error was before, but it was obviously on my end. Thanks again for the help!

Andrew,

Hopefully one more quick question to finalize this problem of mine...as it appears to me, the problem I was running into earlier is still occurring.

While the equation is "rolling" up the data well, it is not referencing the correct CXG product numbers. For example, this is what is currently happening when I implement the formula:

​

Raw Data:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]

---

Upgrade
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]CXG 1
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]CXG 3
[/TD]
[/TR]
[TR]
[TD="align: center"]CXG 4
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Formula Conversion:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Upgrade
[/TD]
[/TR]
[TR]
[TD="align: center"]CXG 1
[/TD]
[/TR]
[TR]
[TD="align: center"]CXG 2
[/TD]
[/TR]
[TR]
[TD="align: center"]CXG 3
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

As you can see, it seems as if the formula is just starting at "1" by default, and counting up by increments of 1 regardless of what the text is showing. I'm not sure if this is a cell reference error on my part, but I've tried playing with the formula earlier today to no avail. Will keep working on it though in the meantime.

Any help is appreciated!

 

[Andrew Poulsom]

Where are CXG 1, CXG 2 and CXG 3 in relation to Upgrade (D11:D19)?

 

[mcmahobt]

Andrew,

The Upgrade D11:D19 are on a separate sheet that contains the raw data. The CXG 1 thru 3 are on the master sheet that is used for the visual representations. Hope this helps! I'll keep working on it as well to see if I can find a solution.

 

[Andrew Poulsom]

That doesn't really answer my question, but if the suffix should be the position of Upgrade in D11:D19 try:

=IFERROR("CXG "&SMALL(IF('Fielding Plan '!D$11:D$19="Upgrade",ROW(A$2:A$10)-ROW(A$2)+1),ROWS(A$2:A2)),"")

 

[mcmahobt]

Andrew,

That did the trick. Whatever you changed seemed to work perfectly. Thank you for all your help!