VLOOKUP Highlight

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
I have a VLOOKUP as follows:

=IF(ISNA(VLOOKUP(A3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!$D:$H,5,0)),0,(VLOOKUP(A3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!$D:$H,5,0)))

I would like to highlight the target cell in '[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'! that is being picked up by the VLOOKUP, any color is okay

I would appreciate any help!

Thx

Sean
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Select the column in the table, go to Format|Conditional Formatting

Select Cell Value Is
Select Equal to
Enter this formula: =VLOOKUP($A$3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!$D:$H,5,0)
Click Format and choose your format

Click Ok, Click Ok again.
 
Upvote 0
Thnx

Thank you, but I am would like to highlight the cell in the target worksheet that is being picked up if I

Select Cell Value Is
Select Equal to
Enter this formula: =VLOOKUP($A$3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!$D:$H,5,0)

A command tells me that I am not allowed to make reference to outside workbooks!

Is there any other ways I might be able to accomplish this type of reference?

Thx
 
Upvote 0
Yeah sorry, you can't reference outside workbooks....I didn't pick that up when I did my testing and copied your formula to my response....:oops:

I think VBA is the only other way.....

Hopefully, someone can help code it for you!
 
Upvote 0
Re: Thnx

Thank you, but I am would like to highlight the cell in the target worksheet that is being picked up if I

Select Cell Value Is
Select Equal to
Enter this formula: =VLOOKUP($A$3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!$D:$H,5,0)

A command tells me that I am not allowed to make reference to outside workbooks!

Is there any other ways I might be able to accomplish this type of reference?

Thx

In some unused cell in MonsterOnline_Impressions_Julye06.xls, enter =Ref.xls!SheetRef!A3

where the references above point to your VLOOKUP keyval cell.

Now reference this cell in your C.F.
 
Upvote 0
Re: Thnx

Thank you, but I am would like to highlight the cell in the target worksheet that is being picked up if I

Select Cell Value Is
Select Equal to
Enter this formula: =VLOOKUP($A$3,'[MonsterOnline_Impressions_Julye06.xls]Monster, July 2006 (2)'!$D:$H,5,0)

A command tells me that I am not allowed to make reference to outside workbooks!

Is there any other ways I might be able to accomplish this type of reference?

Thx

In some unused cell in MonsterOnline_Impressions_Julye06.xls, enter =Ref.xls!SheetRef!A3

where the references above point to your VLOOKUP keyval cell.

Now reference this cell in your C.F.

Jon, should it be =[Ref.xls]SheetRef!A3

it worked for me after I changed it as above.

Great workaround for the external CF! Thanks.
 
Upvote 0
When I enter this reference I does not allow me to switch to another Wb to select the VLOOKUP key val cell to reference an external window ref. pop-ups for me to save a reference?

=[Ref.xls]SheetRef!A3

I would like to reference to '[National Revenue Allocation.xls]Monster Online (2)'!$A$3

Shoud I just do this =D3='[National Revenue Allocation.xls]Monster Online (2)'!$A$3
Then Conditional format if TRUE!

The only problem is I have to resort my data to reference properly..

What do you guys think?

Thx alot you guys are awesome!!
 
Upvote 0
When I enter this reference I does not allow me to switch to another Wb to select the VLOOKUP key val cell to reference an external window ref. pop-ups for me to save a reference?

=[Ref.xls]SheetRef!A3

I would like to reference to '[National Revenue Allocation.xls]Monster Online (2)'!$A$3

Shoud I just do this =D3='[National Revenue Allocation.xls]Monster Online (2)'!$A$3
Then Conditional format if TRUE!

The only problem is I have to resort my data to reference properly..

What do you guys think?

Thx alot you guys are awesome!!

If you enter this in D3 of Monster, July 2006 (2) sheet: '[National Revenue Allocation.xls]Monster Online (2)'!$A$3

Then you conditional format Vlookup would simply be:

=VLOOKUP($D$3,$D:$H,5,0)

....if I am not mistaken, correct Jon?
 
Upvote 0
If it's column D to be highlighted, then

=$D$3

If it's column H, then

=Dx=$D$3

where x is the row number of the current H cell.
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,736
Members
453,616
Latest member
nathancook

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top