Need Help Using INDEX and MATCH with a Dynamic Named Range

Question

Hi I have 2 ListBox's (Purchase_Select_Debtor) & (Purchase_Select_Quantity) on a Userform with a txtBox for Price (txtPrice).

The Code uses Index and Match to return a result based on the Debtor and Quantity Selected.

Below is my current code which works fine, but every time I add a new Debtor I have to alter the code to change the Physical Range.

I would like to set it up using Dynamic Named Ranges so as I add new Debtors the Index/Match Function will still return a Result.

Temp = Application.Index(Sheets("Price_list").Range("A1:I22"), _
Application.Match(Purchase_Select_Debtor.Value, Sheets("Price_list").Range("A1:A22"), 0), _
Application.Match(Purchase_Select_Quantity.Value, Sheets("Price_list").Range("A1:I1"), 0))

    txtPrice.Value = FormatCurrency(Expression:=Temp, _
    NumDigitsAfterDecimal:=2)

I tried this but it didn't work; Type Missmatch Run time Error 13

Temp = Application.Index(Range("Price_list_Table"), _
Application.Match(Purchase_Select_Debtor.Value, Range("Price_list_Debtor_ADD"), 0), _
Application.Match(Purchase_Select_Quantity.Value, Range("Price_list_Quantity_ADD"), 0))

    txtPrice.Value = FormatCurrency(Expression:=Temp, _
    NumDigitsAfterDecimal:=2)

Debugger highlights these 2 lines of code;

    txtPrice.Value = FormatCurrency(Expression:=Temp, _
    NumDigitsAfterDecimal:=2)

The Value of Temp = ""

The chart it's using as a refrence is this;

http://i19.photobucket.com/albums/b152/mantricorb/Chart.jpg

And the Dynamic Named Ranges are as Follow;

Price_list_Table =OFFSET(Price_list!$A$1,0,0,COUNTA(Price_list!$A:$A),9)

Price_list_Debtor_ADD =OFFSET(Price_list!$A$1,0,0,COUNTA(Price_list!$A:$A),1)

Price_list_Quantity_ADD =OFFSET(Price_list!$A$1:$I$1,0,0)

I assume there is something wrong with the Dynamic Named Ranges as it doesn't return a result, any help will be muchly appreciated.

Answer 1

James,

This assumes you have a header and at least one row of data in Column A and that the dynamic range is just one column wide:

=OFFSET(Price_list!$A$1,1,0):OFFSET(Price_list!$A$1,COUNTA(Price_list!$A:$A)-1,0)