PDA

View Full Version : Qty and Time based lookup


Paul_Hossler
03-03-2009, 09:40 AM
I have a pricing sheet that is based on PartNumber, but may also be based on Quantity and/or Date ranges, as well as neither.

I have a UDF that seems to work well (so far: pray2: ), but I can't figure out the worksheet formula equivalent. I need to provide macro-less worksheets to some users.

There is a macro that runs to prepare the source data for the UDF which runs as part of an over all process. It does some checks and adds sentinal values for ending quantities and dates. What I'll do is use VBA to include the .Formula in the copy of the worksheets for the macro-less workbooks.

Attached WB has sample data of the 4 different cases, expected results, and the UDF output.

Thanks

Paul
Bob Phillips
03-03-2009, 10:01 AM
Can you explain the logicof that code in business, in terms of the data elements? It is very hard to follow without spending a long time on it.
Paul_Hossler
03-03-2009, 12:36 PM
Can you explain the logic of that code in business, in terms of the data elements? It is very hard to follow without spending a long time on it.

The cost of a part might depend on the quantity purchased, or the date of purchase or both.

Four cases:

1. Cost does not depend on Date of Qty
2. Cost depends only on Qty
3. Cost depends only on Date
4. Cost depends on both Qty and Date

For example: If the cost of WIDGETs depends on both the qty and the date, there might be the following basis

1 - 10 on or before 31 Sep 2009 = $100 ea
11 - 50 on or before 31 Sep 2009 = $90 ea
50+ on or before 31 Sep 2009 = $80 ea

1 - 10 on or before 31 Sep 2010 = $105 ea
11 - 50 on or before 31 Sep 2010 = $95 ea
50+ on or before 31 Sep 2010 = $85 ea

The costs for many items and many Types (1,2,3, or 4) of items are gathered in one worksheet. I was there was worksheet formula that would "lookup" the cost from the table, based on the part number, the qty, and the date.

So using the above example, the formula should take

part = WIDGET, Quantity = 22, Date = 1 July 2010

(11 < 22 <= 50, and 1 Oct 2009 < 1 July 2010 <= 31 Sep 2010)


and return $95

Thanks

Paul