Vlookup Conditional Formatting

[bloesch1]

I have a workbook for managing a number of projects in a table and have created a visual summary worksheet that denotes where each project is at in our processes.

One of the fields in the table is entitled "pace." This will show if a project is required being fast-tracked. One my summary tab, I'd like to use a vlookup formula in the conditional area to highlight a project with this status. In another cell I have entered:
=VLOOKUP(M8,Table13[[Project Title]:[Pace]],28,0)

Whereas:
-M8 is the cell containing the project's name on the summary page that I want highlighted
-Table13 is my main project table will all the fields I'm drawing from
-28 is the column where Pace is located

This returned the value "Fast Track" to show that the formula was working.

Logically, I then selected M8 for my conditional formatting and entered the formula above. It accepted the formula, but no formats were changed. Do I need to do something different to the formula in the conditional formatting area than I would in a cell?

Also, I'm using Excel 2010.

Thanks in advance!

 

[Desu Nota from Columbus]

I have a workbook for managing a number of projects in a table and have created a visual summary worksheet that denotes where each project is at in our processes.

One of the fields in the table is entitled "pace." This will show if a project is required being fast-tracked. One my summary tab, I'd like to use a vlookup formula in the conditional area to highlight a project with this status. In another cell I have entered:
=VLOOKUP(M8,Table13[[Project Title]:[Pace]],28,0)

Whereas:
-M8 is the cell containing the project's name on the summary page that I want highlighted
-Table13 is my main project table will all the fields I'm drawing from
-28 is the column where Pace is located

This returned the value "Fast Track" to show that the formula was working.

Logically, I then selected M8 for my conditional formatting and entered the formula above. It accepted the formula, but no formats were changed. Do I need to do something different to the formula in the conditional formatting area than I would in a cell?

Also, I'm using Excel 2010.

Thanks in advance!

Quick question(s):
Is the Vlookup located in N8?

Which value do you want to change--value in M8 or the returned value from the Vlookup?

What is the criteria for changing the formatting?

 

[bloesch1]

To answer you questions
-The conditional formatting formula that I entered this into was M8.
-I'm not looking to change any value, but merely change the format of the cell(in this case M8) when the condition of my formula is equal to "Fast Track".
-The criteria is for when the formula is true. I forgot to include the last argument in my original post, so my conditional format formula currently shows:

=VLOOKUP(M8,Table13[[Project Title]:[Pace]],28,0)="Fast Track"

 

[Desu Nota from Columbus]

In your excel table, where is the =vlookup(M8,..............)----In what cell? N8?

Conditional formatting formula would be (for M8) assuming the answer to the above question is yes the vlookup returns Fast Track in cell N8--

=N8="Fast Track"

I'm not familiar with how 2010's conditional formatting has changed vs 2003, but this is the formula/condition you enter for the conditional formatting.

Does this make sense?

 

[bloesch1]

I think I see what you're saying. I currently have a Vlookup formula off to the side beyond the printable area for verification purposes- it looks like it resides in AD8. However, I don't want my conditional formatting formula to reference that cell because M8 is a only sample from a large range.

If I have to do it the way you're suggesting, doesn't that defeat the purpose of having the ability to use a formula/function in conditional formatting?

Thanks,
Brian

 

[Desu Nota from Columbus]

I think I see what you're saying. I currently have a Vlookup formula off to the side beyond the printable area for verification purposes- it looks like it resides in AD8. However, I don't want my conditional formatting formula to reference that cell because M8 is a only sample from a large range.

If I have to do it the way you're suggesting, doesn't that defeat the purpose of having the ability to use a formula/function in conditional formatting?

Thanks,
Brian

It doesn't have to be in the cell adjacent to M8, I was just using N8 as an example. You are correct, it would defeat the purpose of using a formula in conditional formatting. Since you said it was in AD8, the formula would be =AD8="Fast Track"


When you say M8 is only a sample from a large group, is the rest of the group in the same column? (M9, M10, M11,...)

And do you want to do a Vlookup for each (M9, M10, M11,...)with the same conditional formatting as M8?

If yes to both questions:
1. Select cell M8, Copy and Paste Special>Formats for all the cells in Column M (M9, M10, M11,...)

2. Drag the vlookup formula in AD8 down to the corresponding row in Column M

 

[bloesch1]

Yes I agree that technique will work. Even though the rest of my group is not in a column, but rather a range in cells D3:T10. I could create a separate Vlookup off to the side and have the conditional format reference that new area. However, I was hoping to avoid that and only have the vlookup formulas exist solely in the conditional formatting area.

Thank you again for your time and efforts.

 

[Desu Nota from Columbus]

Yes I agree that technique will work. Even though the rest of my group is not in a column, but rather a range in cells D3:T10. I could create a separate Vlookup off to the side and have the conditional format reference that new area. However, I was hoping to avoid that and only have the vlookup formulas exist solely in the conditional formatting area.

Thank you again for your time and efforts.

I just reread what you said and realized that this post is telling you exactly what you know (and don't want to do)


If your data completely fills out the range D3:T10, you can do the same exact process that I mentioned.

Example:

The data range D3:T10 is 17 cells across and 8 cells down (17x8)

Just like the previous case (AD8), pick an area of open cells (17x8), where you can put the =vlookup formula.

Let's say we are starting with AE3 (this cell will correspond to D3), put in it:

=VLOOKUP(D3,Table13[[Project Title]:[Pace]],28,0)="Fast Track"

Drag this formula down 8 cells to AE10, then drag the formula across 17 cells to AU10 (should have the entire range AE3:AU10 highlighted)

Set a conditional format for cell D3--- =AE3="Fast Track", then copy and paste special format to the entire range D3:T10



I just tested this and it works.