Prev: subtract times
Next: check if value exists in pivot table
From: mthead on 2 Apr 2010 16:47
This looks basically like what I want to do, except I need to do it multiple
times at once. I'll try to explain.

I have a weight in cell Sheet1 D11

Sheet2 is a table. Columns A & B are min and max values for a range of
weights. So A1 is 0, B1 is 2.5. A2 is 2.6, B2 is 5. On and on. Column C
is a dollar amount based on that weight. So for row 1 (range 0 - 2.5) the
charge is $1.00. Column D is another dollar amount for each range, so row 1
the charge is $1.50. This repeats for Columns E, F, G. I have 5 zones for
each weight range.

Back on Sheet1 I again have 5 zones. I want the freight cell for each zone
to refer to the table on Sheet2 and extract the appropriate charge (Columns
C, D, E, F, or G) based on the weight indicated in cell D11.

Does that make sense?


"ker_01" wrote:

> mthead-
>
> Here is a framework to get you started- feel free to post back if you have
> trouble adapting this to your needs (and see comments below).
>
> 1. You will probably find it easier to either put your reference range in
> the same workbook, or if that isn't an option, at least have the workbook
> containing the reference range open anytime you open a workbook where you
> need to calculate these values. This code hardcodes your source range for
> convenience.
>
> 2. This code assumes that you will be using numeric values only for your
> comparisons. If you have any numbers stored as text, etc, expect it to return
> an error.
>
> 3. You didn't specify what you wanted to do with overlapping ranges, so I
> assumed which case would be >= vs <=. Feel free to change that if appropriate.
>
> Open the VBE, copy/paste this into a new module. Then in your worksheet, you
> can use it just like any other formula
> =ReturnNewValue(A1) where cell A1 would contain your target value.
>
> Code: (watch for linewrap)
>
> Function ReturnNewValue(IValue)
>
> Dim CompareRange As Variant
> CompareRange = Sheet1.Range("A1:C150") '<-change this to your actual source
> range
>
> For p = LBound(CompareRange) To UBound(CompareRange)
> If IValue >= CompareRange(p, 1) And IValue < CompareRange(p, 2) Then
> ReturnNewValue = CompareRange(p, 3)
> Next
>
> End Function
>
> "mthead" wrote:
>
> > What I need to do:
> >
> > Using Value "A" (let's say 20), lookup on on a table and find the row that
> > 20 falls in the range of column A and column B (let's say column A is 15 and
> > column B is 25) i want to return the value of column C in the same row.
> > There is a possibility of 150 different rows. Also, value A is in workbook
> > Jobs, and the table is in workbork Tables.
> >
> > Any expertise would be appreciated.
First  |  Prev  | 
Pages: 1 2
Prev: subtract times
Next: check if value exists in pivot table