Need help with a formula to validate a cell based on minimum spend please!

[ianbeale]

Hi all, need any help possible. I'm not sure if my wording is correct but i will try to explain the best i can.

I have an excel document providing prices for a glass company. This first table shows a few examples of the list of products i have on the first sheet.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Price Exc VAT[/TD]
[TD]Price Inc VAT[/TD]
[TD]Minimum Spend[/TD]
[/TR]
[TR]
[TD]4mm Clear[/TD]
[TD]45.83[/TD]
[TD]55[/TD]
[TD]7.50[/TD]
[/TR]
[TR]
[TD]4mm Tough[/TD]
[TD]54.17[/TD]
[TD]65[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]6mm Clear[/TD]
[TD]62.5[/TD]
[TD]76[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]


I then have a second sheet linked with it that works out prices and calculates quotes. The product column contains drop down lists for the staff to pick the product from and then a total is worked out based on the sizes provided. However i want to have some form of data validation that looks at the product selected and the total its come but then refers back to the first table to ensure its over the minimum spend.[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Price[/TD]
[TD]Length [/TD]
[TD]Width[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]*drop down list[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]#[/TD]
[TD]*formula based on P,L,W[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm sure its pretty simple to generate something like this and 'im guessing using IF formulas but i cannot get it right and have been trying it for hours and cant get anywhere. Cant someone please help!

Thanks

 

[RCBricker]

ok lets try this

******NOTE change the sheet names in the formula to represent your sheet names

first I am assuming you have your drop down list created.

this starts in Cell A2 of sheet 2

In B2 the following formula

=IF(ISNA(VLOOKUP($A2,Ianbeale1!$A$2:$C$4,3,0)),0,VLOOKUP($A2,Ianbeale1!$A$2:$C$4,3,0))

in Cell E2 (Total Column) enter this formula
for some reason the formula is not saving correctly even using code tags. So I am gonna try breaking it up. the following is all one formula so combine them in the order they appear.

=IF(OR($C2<=0,$D2<=0),"Please enter both Length and/or Width.  Can not be 0 or blank"

add a comma "," between these two parts

IF(SUM($B2*SUM($C2*$D2))<VLOOKUP($A2,Ianbeale1!$A$2:$D$4,4,0),"Minimum Spend Amount not reached",B2*SUM(C2*D2))

then a less than symbol

then this

VLOOKUP($A2,Ianbeale1!$A$2:$D$4,4,0),"Minimum Spend Amount not reached",B2*SUM(C2*D2))