Restrict Data Validation

[Gilliam]

Hello,
I am having an issue restricting a cell to only what is in the drop down.
I have a cell that is data validated as a list to be passed this:
=IF(B4="Lease",$O$3:$O$6,INDIRECT(VLOOKUP($P$21,$O$2:$S$17,5)))

the indirect passes a range for example "O2:O11" which would fill the drop down with numbers 1 through 10. so after the VLOOKUP, it should look like...
=IF(B4="Lease",$O$3:$O$6,INDIRECT(O2:O11))

Including an error alert does not work in restricting the data, nor does it even do anything... I'm wondering if the formula with indirect is the problem?

Any help would be appreciated, thanks.

 

[pjmorris]

Cracked it.

Your validation formula is actually using =IF(B4="Lease",$O$3:$O$6,INDIRECT(VLOOKUP($P$21,hola,5))) not =IF(B4="Lease",$O$3:$O$6,INDIRECT(VLOOKUP($P$21,$O$2:$S$17,5))) and your named range 'hola' is defined as $O$1:$S$17, it makes no difference if you change the name to $O$2:$S$17 neither works. It seems the problem is that an embedded named range is just too much for excel to cope with.

I've sent the spreadsheet back to you.

Regards

Peter

 

[Gilliam]

Thank you!
Much appreciated

 

[pjmorris]

My pleasure, thanks for the feedback.

Computers should do what we want not the other way round!