Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    19

    Unanswered: Extracting column data

    Hi Forum,

    can someone give me a nudge in the right direction with this?
    I have a table linked from Access:
    The table can grow as new articles/suppliers are added, I would like to take this into account in the soloution.
    (Note. Some articles have more than 1 supplier)

    A B .................. P
    Supplier ArticleNo Price
    80001 1235 10,95
    80002 1000 18,99
    80002 1876 15,32
    80001 1000 13,42
    80002 1235 11,30

    How can I construct a formula to extract the price for a given article from a particular supplier?
    I don't really want to resort to VBA with this.

    Thanks for your consideration

    Nick Ellis

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. First with regard to the data, use a dynamic named range, called DataRng:

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

    Now for the formula, you can use at least two approaches. This is the way I typically approach it. It adds an extra column, but not as intense later on. One is to add another column (perhaps to the left of the data, so that data can grow to right). Thus, your supplier is in Column B and Article in Column C. Then label that new column MyLU (in A1), and in A2 put this formula:

    =B2&C2

    copy down as far as data.

    Define this as a named range (MyLU):

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

    And define name range Head:

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

    Then in a cell outside you range (X2 and X3), put the Suppliers for drop down in X2 and Article in X3, then in X4 put this formula:

    =INDEX(DataRng,MATCH(X2&X3,MyLU,FALSE),MATCH("Pric e",Head,FALSE))
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2004
    Posts
    19

    Moving Data

    Now I have read myself clever, thanks for your effort !!!
    Last edited by NickOnTheNet; 10-26-07 at 06:13.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •