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!

 

[Andrew Poulsom]

How does your formula cause the colour to change exactly?

 

[mcmahobt]

That's a bit more intensive. Basically, the spreadsheet that is updated gives details as to what version the product is at, and what year it is to be updated. The IF statement is what prompts the CXG text to show up in a cell, based on whether the Fiscal Years match up. For the color, the following formula is used:

=AND(EXACT(C$3,'Fielding Plan '!$D2),IF(ISBLANK('Fielding Plan '!$F2),FALSE(),VALUE(LEFT('Fielding Plan '!$F2,3))>=5),IF(ISBLANK('Fielding Plan '!$F2),FALSE(),VALUE(LEFT('Fielding Plan '!$F2,3))<10))

Sometimes our vendors leave off what version the product is running under, which is why the IF statements with the nested ISBLANKS are needed. This type of formula is done 6 times, to account for 6 different versions of the product.

 

[Andrew Poulsom]

Why can't you use the same Conditional Formatting formulas for the cells that use INDEX/MATCH?

The problem I have run into with using INDEX/MATCH is that they don't carry over conditional formatting, which I need conserved.

 

[mcmahobt]

Why can't you use the same Conditional Formatting formulas for the cells that use INDEX/MATCH?

The problem that prevents me from doing this (at least from what I've tried) is that the master worksheet pulls data from another sheet within the same workbook to create a visual schedule. So the conditional formatting for a cell on the "visual" worksheet is based off of values from the raw data worksheet.

For example, say a product is set for an update in FY 16. So the text "CXG 4" (denoting the product number in the CXG family) appears under the FY 16 column, since the raw data spreadsheet has FY 16 in an "Update" column. The conditional formatting for the cell referencing the "CGX 4" text on the "visual" worksheet then checks data in a different column on the raw data sheet, but that refers to the CGX 4 product specifically. This will say which version the product is in (version 3.4, 5.1, ect.) and apply a color accordingly.

Therefore, if I were to INDEX/MATCH these values and switch around their absolute reference in the visual worksheet, it affects the version/update year reference in the raw data sheet. Again, there may be a bigger underlying problem with my use of INDEX/MATCH functions, but as I've been spending a good amount of time working on this for the past few days, I'm slowly coming to the conclusion that I'm going about this the wrong way (albeit I inherited this as a task from my boss, so this is not the initial way I would have gone about trying to automate this).

 

[Andrew Poulsom]

I'm not following any of that, sorry. Remember that I can't see your workbook, so terms that are familiar to you aren't at all familiar to me.

 

[mcmahobt]

I'm not following any of that, sorry. Remember that I can't see your workbook, so terms that are familiar to you aren't at all familiar to me.

Andrew,

I'm sorry that was so convoluted. I do realize that without sharing the workbook, a solution would be difficult. Maybe this would be a better method. If you have a minute, take a look at this website: Excel Remove Blank Cells from a Range

In it, they do exactly what I was trying to do previously. The only difference is that my values in cells A2:A10 are returned by an IF statement, not raw data inputs.

So from here, my question is: Would there be a way to construct a macro that does what that website is doing with essentially condensing the data to eliminate blank cells, while preserving the conditional formatting colors I have within my worksheet?

Again, thanks for the help - I hope it's not as confusing this time around.

 

[Andrew Poulsom]

What are the formulas in A2:A10 and what does the source data look like?

 

[mcmahobt]

What are the formulas in A2:A10 and what does the source data look like?

The source data contains IF formulas that populate the cell based on another sheet within the workbook. The formula in cell A1 looks like this:

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

Then in cell A2, the $D11 cell reference changes to $D12, as does CXG 1 to CXG 2 - and so on until cell A10, in which it references $D19 and CXG 8.

 

[Andrew Poulsom]

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.