data validation help

[rodney10954]

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

 

[rodney10954]

the min amount is 2000

 

[pedro-egoli]

I set up a spreadsheet as follows

Book1
	A	B	C	D	E	F
1				0-80	81-85
2	AGE0-80	AGE81-85	amount	amountaccepted	amountaccepted
3	45			18000
4		82			15000
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

 

[Peter_SSs]

Maybe Data Validation like this?

Excel Workbook
	A	B	C	D	E	F	G
1	Age	Amount			Age	Min Amount	Max Amount
2	50				0	2,000	20,000
3					81	2,000	15,000
4
Data Validation

#VALUE!

 

[pedro-egoli]

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

 

[Peter_SSs]

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.

 

[rodney10954]

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