Complex data validation problem

[teachman]

Hello and Happy New Year to y'all!

I thought I'd start my New Year off right with a data validation problem. Setting: cell K6 holds 2 char state abbreviation; cell AH12 holds elevation at a specific latitude/longitude.

Problem, I want to make sure the user doesn't enter an elevation value (AH12) that is outside the elevation range found in the state (K6). I have a lookup table with three columns, ST, Max Elev, and Min Elev. I've set the table as the named range StMaxMinElev. That's about as far as I can get. I suppose I could use a series of nested IF statements, but there must be a better way.

I would appreciate any help y'all can give me.

Thanks,


George Teachman

 

[Marcelo Branco]

Data > Data Validation > Data Validation > Custom

Try this formula
=AND(VLOOKUP(K6,StMaxMinElev,2,0)>=AH12,VLOOKUP(K6,StMaxMinElev,3,0)<=AH12)

M.

 

[teachman]

Marcelo,

Thanks for the help. That is a lot easier than trying to do it with vba scripting.

George