+ Reply to Thread
Results 1 to 4 of 4

Lookup to return an array

  1. #1
    Registered User
    Join Date
    10-30-2006
    Posts
    2

    Lookup to return an array

    Hi there forumers, a slightly curly lookup question.

    I want the used to be able to enter a value (in this case an assembly number). Once they hit enter i want to check another sheet for the number and return an array (the part numbers contained in the assembly and the quantity used in the assembly).

    An Example:

    the user types: AS30

    In another sheet is a list of the parts contained in AS30. Part numbers start with an S:
    s1 s2 s3 s4 PART NUMBERS
    1 3 2 1 QUANTITY

    Once the AS number has been located, I want the array transferred to another sheet so that calculations can be done. Am I asking too much? Is it time i learnt how to use VB properly?

    Thanks alot for any advice.

    Evan

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by evanbb
    Hi there forumers, a slightly curly lookup question.

    I want the used to be able to enter a value (in this case an assembly number). Once they hit enter i want to check another sheet for the number and return an array (the part numbers contained in the assembly and the quantity used in the assembly).

    An Example:

    the user types: AS30

    In another sheet is a list of the parts contained in AS30. Part numbers start with an S:
    s1 s2 s3 s4 PART NUMBERS
    1 3 2 1 QUANTITY

    Once the AS number has been located, I want the array transferred to another sheet so that calculations can be done. Am I asking too much? Is it time i learnt how to use VB properly?

    Thanks alot for any advice.

    Evan
    In the second sheet, does the 'AS30' appear on many rows each with a separate ('S') Part number? Or is it held as an item header followed by rows that have nothing in the column (until the next assembly)

    or are the part numbers across the row with the quantity underneath?

    If not, how are the parts held?

    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    10-30-2006
    Posts
    2

    Further information

    Currently the second sheet is a reference sheet defining the assembly contents. So each AS number will only occur once, as a header for a row. Then the part numbers and their quantity stream out left along the row.

    Eg
    AS# Part qty part qty part qty
    AS30 S10 2 S11 1 S12 1

    This is negotiable though, if there's a more efficient format it can easily be changed.

    Thanks again

    Evan

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by evanbb
    Currently the second sheet is a reference sheet defining the assembly contents. So each AS number will only occur once, as a header for a row. Then the part numbers and their quantity stream out left along the row.

    Eg
    AS# Part qty part qty part qty
    AS30 S10 2 S11 1 S12 1

    This is negotiable though, if there's a more efficient format it can easily be changed.

    Thanks again

    Evan
    My VB code is so uncouth, but you should get an idea from the attached.

    Select the first entry from the bemenu and type AS30 into the first box

    From there you can, of course, check/amend the parts required, and press the (uncoded) button to save to the next blank line on a sheet.

    Was this the idea you are looking for? - if so, yes, it's worth the effort for VB.

    note, on Sheet2 you can add parts up to column P/Q

    Added - new .zip

    The Button now works, and allows a simple description to be added.
    Formula can be auto-added (via the button) to Sheet1 as required.

    hth
    ---
    Attached Files Attached Files
    Last edited by Bryan Hessey; 10-31-2006 at 03:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1