Data Validation looking up for a certain string in another cell and ignoring blanks

[Foxtrots]

Hi Excel Experts,

Could some one help me with data validation for cell D2 in sheet 2, cant figure out where I am getting it wrong.

Sheet 1 has three options in cell C2, I no longer want row K2:X2

Many thanks.

Manu

 

[bosco_yip]

Hi,

1] Revised your Sheet2, D2 Data Validation formula to :

=OFFSET($K$2,($C$2=0)*2+1,MATCH($B$2,$L$2:$X$2,0),IF($C$2=0,OFFSET($K$4,0,MATCH($B$2,$L$2:$X$2,0)),1))

or,

=OFFSET($K$2,IF($C$2=0,3,1),MATCH($B$2,$L$2:$X$2,0),IF($C$2=0,OFFSET($K$4,0,MATCH($B$2,$L$2:$X$2,0)),1))

2] See attached file.

Regards
Bosco

 

[Foxtrots]

Dear Bosco,

Many thanks for your help.

Sorry its just one problem, I should have changed the "-" to No in K2.

When I do that the validation no longer works, please help.

Attached is the file

Kind Regards

Manu

 

[Foxtrots]

Bosco,

I changed the validation to =OFFSET($K$2,IF($C$2="Yes",1,IF($C$2="No",3,"")),MATCH($B$2,$L$2:$X$2,0),IF($C$2="No",OFFSET($K$4,0,MATCH($B$2,$L$2:$X$2,0)),1))

And it works well.

Thanks again for all your help.

You are a star.

Manu

 

[Peter Bartholomew]

I think that is a contender for the most complex validation sequence I have ever seen. Just to understand what it was doing I worked through it, finishing with 'FieldValidationList' that refers to
= OFFSET( Options.Base,
IF(Choice,3,1),
FieldValidationLists.Index,
IF( Choice, FieldValidationList.Count, 1) )
but that was only to help me read the formulas.

Something I noticed that struck me as odd was the location of the final entry in each validation list using
= INDEX( MATCH( 2, 1/(FieldValidationLists<>"") ), 0 )
Assuming the MATCH returns a number, what does the INDEX do?

 

[Foxtrots]

Hi Excel Experts,

Is it possible to turn two of the cells in the attached sheet to Red when something other than those from the drop down list is pasted into them.

I use macros to check for Validation and formatting errors as shown in the sheet.

The format check for A2 and C2 work fine, I just don't seem to find a way to get B2 & D2 to work.

Peter, I didn't see your message before. can you suggest another way of achieving the validations for B2 and D2.

Many thanks in advance.

Regards