+ Reply to Thread
Results 1 to 8 of 8

Working with Multiple Xls files

  1. #1
    Registered User
    Join Date
    04-22-2005
    Posts
    4

    Working with Multiple Xls files

    I'm working with an extract of a database and cuting and pasting information from the extract to another file.
    The work is repetative as all hell and I know there is a better way to get the data from one to the other but I can't figure out how.

    I need 4 cells from one sheet placed into the other sheet if the serial numbers match.

    All input is helpful.
    Thanks
    Martello

  2. #2
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Check out =VLOOKUP to see if it will do what you need. Certain limitations apply, e.g. the lookup table must be sorted ascending by the lookup values (serial numbers in this instance).

    Assuming your 'extract' is on Sheet1 in an Excel workbook and you are trying to 'cut/paste' to Sheet2 in the same workbook, your formulas on Sheet2 would look something like this (where your list of serial numbers are in A1:A1000 on both sheets:

    In cell B1 =VLOOKUP(A1,Sheet1!$A$1:$Z$1000,2,0)
    In cell C1 =VLOOKUP(A1,Sheet1!$A$1:$Z$1000,3,0)
    In cell D1 =VLOOKUP(A1,Sheet1!$A$1:$Z$1000,4,0)
    In cell E1 =VLOOKUP(A1,Sheet1!$A$1:$Z$1000,5,0)

    Modify the ranges as necessary for your data and add a workbook reference if your 'extract' is in another workbook (=VLOOKUP(A1,[myextract.xls]Sheet1!$A$1:$Z$1000,4,0)

    HTH

    Bruce
    Last edited by swatsp0p; 04-22-2005 at 12:33 PM.
    Bruce
    The older I get, the better I used to be.
    USA

  3. #3
    Registered User
    Join Date
    04-22-2005
    Posts
    4

    close but no

    Bruce,
    Thanks for the tip. I'm testing it out and not going well. Some of these serial numbers are letters and numbers.
    I'll check back in.

    Martello

  4. #4
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    How is it not going well?

    VLOOKUP doesn't care whether the value to find is numeric, text or a comination (which would actually be text).

    e.g. it can find 123456 just as easy as it can find 123abc

    Of course the entries in both cases *must* be identical, no exta spaces, etc.

    HTH

  5. #5
    Registered User
    Join Date
    04-22-2005
    Posts
    4
    Wow that is handy.
    It works almost. It is now returning data, just not always the right stuff after I past the formula into the next couple cells.

    See the thing is that my data array (table) keeps creeping by one in both the row and column number when I cut and past the formula into the next row. I tried to select the entire sheet but that only works for one cell at a time.

    Martello
    Last edited by Martello; 04-22-2005 at 03:56 PM.

  6. #6
    Registered User
    Join Date
    03-25-2005
    Posts
    23
    you may want to use an absolute reference on A1 in Bruce's example

    $A$1

  7. #7
    Registered User
    Join Date
    04-22-2005
    Posts
    4
    That did it!
    You guys rock. I'll see you later.

    Martello

  8. #8
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Martello: You are very welcome. We look forward to seeing you again. Thanks for the feedback.

    Davey: Thanks for jumping in with the absolute reference tip!

    Cheers.

    Bruce

+ 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