Best Solution for 3 search criteria
 

I have a table with columns 1 & 2 for size range (i.e 0 - 200mm, 201 - 400mm,
column 3 showing a number (0,1 or 2), columns 4 & 5 holding quantity ranges
and a cost in column 6.

I need to find the correct range based on size, then narrow the range based
on the number in column 3 and finally select the correct row for the price
based on quantity.

Can I do this ?

Best Solution for 3 search criteria
 

Hi,
could you please send an example of what you want to achieve including a
sample of your table mentioned below

"gibbylinks" wrote:

I have a table with columns 1 & 2 for size range (i.e 0 - 200mm, 201 - 400mm,
column 3 showing a number (0,1 or 2), columns 4 & 5 holding quantity ranges
and a cost in column 6.

I need to find the correct range based on size, then narrow the range based
on the number in column 3 and finally select the correct row for the price
based on quantity.

Can I do this ?

Best Solution for 3 search criteria
 

A series of multiplication inside the sumproduct command could help you to
find your solution. Maybe something like this will solve:

=sumproduct((A$2:A$100="lower size")*(B$2:B$100="higher
size")*(C$2:C$100="0,1,2")*(D$2:D$100="cost"))
In column A and B you have size range, in column C you have 0,1,2 numbers
and in column D you have cost.
Sumproduct is a powerful Excel command and you can use it for multi-column
conditional select.
For more info on sumproduct you can go to:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html



"gibbylinks" wrote:
I have a table with columns 1 & 2 for size range (i.e 0 - 200mm, 201 - 400mm,
column 3 showing a number (0,1 or 2), columns 4 & 5 holding quantity ranges
and a cost in column 6.

I need to find the correct range based on size, then narrow the range based
on the number in column 3 and finally select the correct row for the price
based on quantity.

Can I do this ?

Best Solution for 3 search criteria
 

Size Slots Quantity
195mm 0 4

Table

Size Slots Quantity Cost
from to from to
0 200 0 0 1 £1.50
0 200 0 2 6 £1.00
0 200 0 7 18 £0.75
0 200 1 0 1 £2.50
0 200 1 2 6 £2.25
0 200 1 7 18 £2.00
201 400 0 0 1 £1.75
201 400 0 2 6 £1.50
201 400 0 7 18 £1.25
201 400 1 0 1 £2.75
201 400 1 2 6 £2.50

This repeats down with sizes 401-600, 601-800 the slots repeat with the
quantities also repeating. So the one above would cost £1.00 each

Hope that's clear enough


"Eduardo" wrote:


Hi,
could you please send an example of what you want to achieve including a
sample of your table mentioned below

"gibbylinks" wrote:

I have a table with columns 1 & 2 for size range (i.e 0 - 200mm, 201 - 400mm,
column 3 showing a number (0,1 or 2), columns 4 & 5 holding quantity ranges
and a cost in column 6.

I need to find the correct range based on size, then narrow the range based
on the number in column 3 and finally select the correct row for the price
based on quantity.

Can I do this ?

Best Solution for 3 search criteria
 

Have you tried using an Autofilter?

"gibbylinks" wrote:

I have a table with columns 1 & 2 for size range (i.e 0 - 200mm, 201 - 400mm,
column 3 showing a number (0,1 or 2), columns 4 & 5 holding quantity ranges
and a cost in column 6.

I need to find the correct range based on size, then narrow the range based
on the number in column 3 and finally select the correct row for the price
based on quantity.

Can I do this ?

Best Solution for 3 search criteria
 

Just tried Autofilter, yes it works, but I want to make it simple for other
users. Just put size ,quantity and select slots from drop down.

"ArcticWolf" wrote:

Have you tried using an Autofilter?

"gibbylinks" wrote:

I have a table with columns 1 & 2 for size range (i.e 0 - 200mm, 201 - 400mm,
column 3 showing a number (0,1 or 2), columns 4 & 5 holding quantity ranges
and a cost in column 6.

I need to find the correct range based on size, then narrow the range based
on the number in column 3 and finally select the correct row for the price
based on quantity.

Can I do this ?

Best Solution for 3 search criteria
 

Record a macro while doing your autofilter routine and then modify to allow
for the variables

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gibbylinks" wrote in message
...
Just tried Autofilter, yes it works, but I want to make it simple for
other
users. Just put size ,quantity and select slots from drop down.

"ArcticWolf" wrote:

Have you tried using an Autofilter?

"gibbylinks" wrote:

I have a table with columns 1 & 2 for size range (i.e 0 - 200mm, 201 -
400mm,
column 3 showing a number (0,1 or 2), columns 4 & 5 holding quantity
ranges
and a cost in column 6.

I need to find the correct range based on size, then narrow the range
based
on the number in column 3 and finally select the correct row for the
price
based on quantity.

Can I do this ?