vlookup and conditional formatting

hello, i have 2 tabs on a spreadsheet. 1 contains a lot of booking data and one contains a list of numbers. i want to be able to use a vlookup to search for any of these numbers in the data sheet and highlight the cell.

Im not sure what is the best way to approach it.

a very basic sample file attached

Hi fentiger79,

Here's my attempt... I'm sure there's something more elegant, but this should work...

Goto to your first number (B2), then choose Conditional Formatting on the Home tab.

Next, choose New Rule..., then choose Use a formula to determine which cells to format...

Drop this formula in the text box... =NOT(ISNA(VLOOKUP(B2,booking!$B$2:$B$20,1,0)))

Choose a format under the fill tab... say bright yellow...

Copy this format to the rest of your cells...

Let me know if this doesn't work...

Later,
Dennis

thanks dennis but that still didnt work

sample with highlights.xls

I've attached what you sent... please see if you still don't follow...

Hi fentiger79

I noticed you uploaded a .xls file!

cheers kevin its kinda what i wanted. i need to highlight the booking sheet if any of the direct numbers appear in the list. also is it possible to highlight the cells rather than put found next to them

Hi fentiger79,

The vlookup I gave was backwards...

Try this formula for the conditional formatting instead...

=NOT(ISNA(VLOOKUP(B2,direct!$B$2:$B$13,1,0)))

You should be in cell B2 in the 'booking' tab, then go to Conditional Formatting...

Let me know if you need more directions from here...

Dennis

hi dennis, does that work for 2007 i currently have 2003 at home and it doesnt work on that. Wont let me reference another sheet within conditional formatting

Can you put the data on the same sheet?

Hi djapigo

Use Conditional formatting, that will high light the cells.

Formula:

Please Login or Register  to view this content.

Conditional Formatting

wont let me use reference to another sheet in the conditional format. maybe it will on 2007 but im currently using 2003.

Goggle,there's plenty of articles on conditional formatting and referencing another sheet with Excel 2003. I do not have a problem with Excel 2010, so should be no problem with 2007.

fentiger,

I agree with Kevin, conditional formatting should allow it...

I wonder if you have the absolute references on your formula? Make sure about the $ signs around your formulas, that will make a difference... Follow either one of our formulas, either should work...

Dennis

well heres what i got at the minute. ideally i want to put the formula in the conditional format.

Hello all
Don't know about 2010 but 2007 and 2003 will not let you reference another sheet in Conditional Formatting. You can however, create a named range for your numbers and reference that, for example:

Please Login or Register  to view this content.

should give you the desired result.

Hope this helps.
DBY

also how do i highlight the whole row rather than 1 cell

Hello
Select the range of rows you wish to highlight and amend the Conditional formatting formula so as column B is absolute:

Please Login or Register  to view this content.

DBY

I learn something everyday... thanks, DBY

please explain DBY. my formula is =FIND("WEB",C1)>0 to highlight a cell. i wanna use it for the entire row

So, what's the "WEB" for then, you have numbers in your ranges!

Like Kevin I'm a little confused. I thought you wished to reference the numbers on your 'direct' sheet. But if you want to use your formulas in column C on the 'booking' sheet, then amend your Conditional formatting formula with an absolute reference:

Please Login or Register  to view this content.

and amend the 'Applies to' range to:

Please Login or Register  to view this content.

DBY

DBY, originally i wanted to highlight any numbers in the booking sheet that also appear in the direct sheet. I was told you cant reference another sheet in conditional formatting. Therefore i used a formula to type WEB in the next coloumn. then used a conditional format to highlight the cell if it displays web. now i would like to change it so it highlight the entire row but dont know how. Also if you know of an easier way to do this please explain.

whats an absolute reference?

Hi

In post #14 I mentioned google for Conditional formatting in Excel 2003, here's Google search

Google

Cell Referencing

Hello fentiger
It might be a good idea to do a bit of research as Kevin suggests. In the meantime take a look at my attached reply. It gives some basic instructions as to how to proceed.

regards
DBY

thanks DBY that is the kind of thing i want to do. just i dont understand how you set up the name of the range you use.

whats an absolute reference?

thanks DBY that is the kind of thing i want to do. just i dont understand how you set up the name of the range you use.

As I said before, Google. There is a wealth of information on the web.
Try here for starters
http://office.microsoft.com/en-gb/excel-help/

Every one on here is willing to help, but.
It seems to me that you don't want learn!

im willing to learn, i have built alot of stuff by looking on here. but i too have a limitation.

Hello fentiger
Noticed that you just marked the thread as solved. Glad you've worked it out. I was about to provide you with a link to a site which explains how to create a Named Range as I outlined in my reply file on the 'direct' Sheet. Anyhow, here's the link for future reference:

http://spreadsheets.about.com/od/exc...amed_range.htm

Regards
DBY