+ Reply to Thread
Results 1 to 7 of 7

get last row of external workbook for vlookup

  1. #1
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    get last row of external workbook for vlookup

    I have the following vlookup and it works fine, however to get this to work in other sheets I will need to have the R25 to be a last row that contains values, rather than a hard coded Row 25. Can this be done? Basically I need to pull the range using last row from the external spreadsheet rather than hard coding the R value in the vlookup.

    "=VLOOKUP(RC[-6]," & Wb & "Product Receipt'!R2C1:R25C18,16,FALSE)"

    Obviously the Wb is a variable, this variable contains the path and filename that the data come from in the vlookup.

    the starting point of the range will always be constant, but the end row will always be different, hence the need for this functionality.

    Any help would be great, thank you.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: get last row of external workbook for vlookup

    cant you just put R2000 or something?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: get last row of external workbook for vlookup

    no, that would not work in vlookup, would return a value of #NAME? rather than the values I need from the range

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: get last row of external workbook for vlookup

    why? you are just making the range bigger R2C1:R2000C18

  5. #5
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: get last row of external workbook for vlookup

    not sure why it did that, but it did. Also, there are some sheets that will have close to 1Mil records, so 2000 would def not work. When I did the test I used 2000000 and got the #NAME? issue.

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: get last row of external workbook for vlookup

    well there aren't 2000000 rows 1048576 is the limit

  7. #7
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: get last row of external workbook for vlookup

    sorry, typo, 200000

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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