Validation and Conditional Formatting

Ivan F Moala

MrExcel MVP

Joined

Feb 10, 2002

Messages

4,209

• May 2, 2003

• #2

Use a Named range
Look this up in your Help files...but basically highlight the list and in the namebox droplist type in a discriptive name for this Data list eg MyList
Then in your Validation = MyList

 

Upvote 0

K

Kreang

New Member

Joined

May 2, 2003

Messages

24

• May 2, 2003

• #3

Result of using name range still can not use Validation and Conditional Formatting.

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• May 2, 2003

• #4

Ivan's suggestion should work. Please repost exactly what you did, including formulas for your Conditional formatting and Data Validation

 

Upvote 0

K

Kreang

New Member

Joined

May 2, 2003

Messages

24

• May 2, 2003

• #5

Book1.xls Sheet1 and Named Range= "Holiday" (A1:B366)

A B
1 2/5/03 xxx
2 3/5/03
3 4/5/03
4 5/5/03 yyy
5 6/5/03

Book2.xls

A
1 2/5/03 (in cell A1, I use formula =IF(VLOOKUP(A1,[Book1.xls]Sheet1!
Holiday,2,FALSE)="",0,1)=1
2 3/5/03
3 4/5/03
4 5/5/03
5 6/5/03

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• May 2, 2003

• #6

Please clarify what are you trying to validate, and where are you applying the Conditional Formating -- that is what you said in your original post you are trying to do -- right!

 

Upvote 0

K

Kreang

New Member

Joined

May 2, 2003

Messages

24

• May 3, 2003

• #7

I want to check Holiday from book1 (Holiday data workbook) , if check date in book2 and column B in book1 <>"" it means that date is Holiday, I want to use Conditional Formatting to highlight that date in book2

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• May 3, 2003

• #8

Kreang said:

I want to check Holiday from book1 (Holiday data workbook) , if check date in book2 and column B in book1 <>"" it means that date is Holiday, I want to use Conditional Formatting to highlight that date in book2

Click to expand...

How about trying ...

Conditional Formating for cell B1

Formula Is=isnumber( VLOOKUP($B1,Holidays,1,0))

 

Upvote 0