WOO HOOOOOOOOOOOOOOO

TOP MAN...........
--
Martin


"John C" wrote:

where is your comma? and double quotes?
OR(A3="",C3="")

And if you are copying down, this will change from A3="",C3="" to
A4="",C4="" and A5="",C5="" etc.

But that is A3="" (note 2 double quotes), then a comma, then C3=""

Hope that helps.


--
John C


"Martin Panter" wrote:

Hmmm...... I get the Vlookup and Lookup bit, but still unsure on the
(OR(A3="C3"),"". As this is a formula that will be copied over many cells I'm
not sure if I have translated the syntax correctly. Intitial result returned
is '#NAME?'

Taken straight from the sheet where A3 is the part number and C3 is the
variable quantity...

=IF(OR(A3="C3"),"",VLOOKUP(A3,'EMEA
SRP'!A:H,LOOKUP(qty,{0,4;26,4;101,6;501,7;1001,8}) ,FALSE))


--
Martin


"John C" wrote:

=IF(OR(part#="",qty=""),"",VLOOKUP(part#,Tbl_Parts ,LOOKUP(qty,{0,3;26,4;101,5;501,6;1001,7}),FALSE))

Since all qty discounts break at the same quantity, you could do something
like above. I check to ensure first that I have a part number and a quantity
entered. Then, using the part number, I look up the price. The LOOKUP
determines which column I pull the pricing from.

Hope this helps.
--
John C


"Martin Panter" wrote:

John,

Unfortunatley not. The reference table in TAB 2 is set up (read only), as
follows

Part No Description 0-25, 26-100, 101-500, 501-1000, 1000
AAAAA Widget $5.00 $4.00 $3.00 $2.00 $1.00
BBBBBB Gromet $9.00 $8.00 $7.00 $6.00 $5.00

etc.

Any suggestions?
--
Martin


"John C" wrote:

How is your pricing portion set up? Do you have quantities down the column,
and part numbers as row headers? This would be an easy way to do it. Because,
then, no matter the quantity discounts, as long as you set up your table
properly, you'll be good.
I.E.:

Part1 Part2 Part3
0 25.00 75.00 5.25
25 23.75 75.00 5.25
30 23.75 67.50 5.25
50 23.75 67.50 4.95

Basically, in this scenario, Part1 is $25.00, unless they order more than
25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless
they order 50 or more. The key is to ensure the 'quantities' down the left
column are in ascending order, and starting with 0.

So your lookup would be:
=VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1)

This assumes your pricing table is a separate tab. Note the last '1' in the
VLOOKUP, this will allow it to search for the best 'qty' in left column,
without going over.

Hope this helps.



--
John C


"Martin Panter" wrote:

Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part
Number' in cell A1, Vlookup will return its description in cell A2 (no
problems so far), and then if I enter a quantity in A3, cell A4 will identify
the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or
1000 and return the correct unit price for the part number and quantity
break - which is identified and held in a separate table (on TAB 2),
alongside the Part Number and Descriptions.

Martin