Cell Data Validation using LOOKUP?

[Chunky_22]

Hi

I have a table in Sheet 2, A3:E21. Column A is Company Names, Column E is 'funds available' dollar values

In Sheet 1, I need users to enter how much they have spent with each Company. In column D is a drop down list of company names, and column E is where you enter what you've spent.

I would like to use something like Data Validation on the cell in Column E. I need it to look at what company name the user has selected from the drop down, and then VLOOKUP that company on Sheet 2 to see if the value they have entered exceeds the funds available. If they have exceeded the amount available, a pop up or some other notification needs to be presented to the user.

If this is achievable with macros that's also no problem, I have no skills in this area but can copy and paste code

Thanks in advance for your help.

 

[Special-K99]

Sheet1!D1 is where you enter the company name
Sheet1!E1 is where you enter the value

Use Custom Data Validation with formula

=OR(D1="",E1=0,E1<=VLOOKUP(D1,Sheet2!A$3:E$21,5,0))

NOTE: Data Validation can be bypassed by pasting a value into the cell containg the validation

 

[Chunky_22]

Perfect. Thanks for that, that's exactly what I need.
Cheers