Results 1 to 7 of 7

Thread: lookups (2003)

  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    lookups (2003)

    i have 2 spread sheets. #2 has 2 columns, column A has part numbers, column B has prices. This spread sheet is periodicaly updated with revised part numbers and prices. For example, a part number may change from 1234567 to 3j3126, price will not change, or it may.... #1 spreadsheet has a vlookup formula that looks up a price for a part number in spreadsheet 2 based on a part number chosen in spreadsheet 1. ... 2 questions, first, since a vlookup formulae requires that the lookup field be in numerical order, what can be done to automatically make this happen, and #2, how do i update the part number in #1 spreadsheet when the number in #2 spreadsheet is updated. (#2 spreadsheet resides on a lotus notes server and is updated by a sequel, then replicated by the salesmen, once a week, Both spreadsheets reside on sales laptops)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookups (2003)

    If you add a 4th argument FALSE to the VLOOKUP formulas, the lookup range doesn't need to be sorted on the first column.

    I don't see how the part numbers could be updated. It could be done if #1 and #2 were open at the same time and if part numbers in #2 were edited manually. But not if #2 is simply replaced by a new version.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: lookups (2003)

    1 way for Question2 to work without making any changes to #1 is to create a third sheet with a crossreference list. If #2 has the "new list", you will need (in#3) a list to show each of the previous part numbers and what the "new" part number is. So you would do a dual lookup. #1 would take its "old" part number, lookup the new part number (in #3) and then use this as the lookup value in #2 to get the current price.

    So whenever #2 is updated, one would have to just add the changed part numbers to #3 (old/new). You could create a new #3 whenever a new #2 is created (they could even be on the same sheet if desired.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookups (2003)

    is there a way to notify #1 spreadsheet if a part # has been changed on #2, so the salesmen can make the change on #1?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookups (2003)

    You'd have to do something along the lines of Steve's proposal: create a third workbook that can compare the old and new versions of #2.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookups (2003)

    It's getting confusing

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookups (2003)

    That is caused by your setup. Your #2 workbook is an export from another system. If you just overwrite each existing version with a new one, you have no way to tell what has changed, unless you create a worksheet that lets you compare the old and new values.

Posting Permissions

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