Excel: Allow range of values in a cell 1

[ShabanaHafiz]

I have compensation details in a sheet PayScale. Columns are A to F. Rows 1 to 4:

Salary Level upto 2,000 upto 5,000 upto 10,000 upto 25,000 upto 1,000,000
Salary Ranges
Minimum Salary 0 2,001 5,001 10,001 25,001
Maximum Salary 2,000 5,000 10,000 25,000 1,000,000

In the second sheet CompensationCalculator, Columns are A to B, Rows 1 to 2:

Column A:
Salary Level
Gross Salary

Column B:
B1= Data -> Validation -> List
Source = SalaryLevel (Named Range, refers to PayScale!$B$1:$F$1)

What I need to accomplish is when the user selects any SalaryLevel in B1, he should be allowed values for Gross Salary only within the Minimum and Maximum Salary Range.

For B2, I tried Data -> Validation -> Whole number.
Data = between
Minimum = HLOOKUP($B$1,Compensation,3,FALSE)
Maximum = HLOOKUP($B$1,Compensation,4,FALSE)
Compensation is a named range, refers to PayScale!$A$1:$F$4

But B2 is allowing values outside the range. For example, when I selected upto 5,000 in B1, it is allowing 2,000 and 5,001.

 

[Fenrirshowl]

Try naming the salary level, min and max salary ranges. If Playing with the problem I used sallevel, minsal and maxsal as the names, one answer would be as follows:

For B2, Data -> Validation -> Whole number.
Data = between
Minimum = index(minsal,match($B$1,sallevel,0))
Maximum = index(maxsal,match($B$1,sallevel,0))

Hope this helps