# data validation help

#### rodney10954

##### New Member
I have a cell that users are to input a dollar amount
based on below
so they would put there age in and then when they put in a dollar amount
i would want it to be restricted to these
Coverage Amount:
Maximum Coverage Amounts allowable:
Age 0-80 \$20,000
Age 81-85 \$15,000
any help would be great

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
the min amount is 2000

I set up a spreadsheet as follows
Book1
ABCDEF
10-8081-85
2AGE0-80AGE81-85amountamountacceptedamountaccepted
34518000
48215000
5
6
7
Sheet1

In columns A and B Data validation has been set up under Settings/whole number as follows
A set between 0 and 80
B set between 81-85

Error alert has been recorded as
INCORRECT AGE FOR THIS CELL, CANCEL ENTRY AND ENTER IN CORRECT CELL

In colums D and E validation has been set up under Settings/whole number with the respective range for that age group and the Error alert reads

AMOUNT ENTERED OUTSIDE THAT PERMITTED. CANCEL AND ENTER WITHIN CORRECT RANGE
Pedro

Maybe Data Validation like this?

Excel Workbook
ABCDEFG
1AgeAmountAgeMin AmountMax Amount
25002,00020,000
3812,00015,000
4
Data Validation
#VALUE!

Peter,
That is a more professional way to solve the problem.

Never used VLOOKUP and would appreciate what each formula is actually doing.

Thanks
Pedro

Never used VLOOKUP and would appreciate what each formula is actually doing.
The best place to start would be to look up VLOOKUP in Excel's built-in Help. There you will find quite a good explanation and several examples.

Basically I am using the VLOOKUP to find the age from cell A1 in column E and bring back the min and max allowable values from columns F and G respectively. When you look up VLOOKUP in the Help, it will explain how it operates when the 'looked for' value is not found.

thanks guys
i figured it out last night
=OR(AND(C12<=80,C18>=2000,C18<=20000),AND(C12>=81,C12<=85,C18>=2000,C18<=15000))

the min amount is 2000
and the max is set to 15000 for over 80 and 20000 for all years lower
set something similar in the age field

Replies
3
Views
306
Replies
19
Views
284
Replies
1
Views
176
Replies
1
Views
125
Replies
0
Views
139

1,196,048
Messages
6,013,091
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back