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.

Product	Price Exc VAT	Price Inc VAT	Minimum Spend
4mm Clear	45.83	55	7.50
4mm Tough	54.17	65	10
6mm Clear	62.5	76	12

<tbody>
</tbody>

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.

Product	Price	Length	Width	Total
*drop down list	#	#	#	*formula based on P,L,W

<tbody>
</tbody>

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))