Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VLOOKUP & Dynamic Ranges

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to create a dynamic range that
    will change each time my project list changes.

    I have seen some code that allowed me to capture the range as the following:
    =OFFSET($A$1,0,0,COUNTA($A:$A),4)

    (In the Define Name box I called it myRange)

    What I need to do is: have another workbook
    (when it is called from the first workbook) use the VLOOKUP table to be able to use myRange to find the value I want. I keep
    getting errors. Any suggestions, I am kind of new at this. Thanks

    Below is a sample of the first Workbook

    FY 2001 MY Project List
    PROJ NBR PROJ NAME BR NBR BR CHIEF
    0176 Demographic LAN Support 18 Lau
    0902 Division InterProject 02 Kreilick
    0906 CPS 44 Tucker
    0937 LEHD 04 Prevost


    [ This Message was edited by: tryingexcel on 2002-04-03 08:00 ]

    [ This Message was edited by: tryingexcel on 2002-04-03 08:20 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-03 07:59, tryingexcel wrote:
    I am trying to create a dynamic range that
    will change each time my project list changes.

    I have seen some code that allowed me to capture the range as the following:
    =OFFSET($A$1,0,0,COUNTA($A:$A),1)

    (except I changed the column from 1 to 4)
    In the Define Name box I called it myRange

    What I need to do is: have another workbook
    (when it is called from the first workbook)use the VLOOKUP table to be able to use myRange to find the value I want. I keep
    getting errors. Any suggestions, I am kind of new at this. Thanks



    [ This Message was edited by: tryingexcel on 2002-04-03 08:00 ]
    The formula you posted does not refer to a sheet in which your 4-column table should be. Be aware of the fact if column A has any blanks interspersed with data, you'll end up with "myRange" which is not computed correctly.

    You could post 5 rows (including the column headings/labels) of the range to which "myRange" refers for further help.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mr. tryingExcel

    try using Insert name define

    type in a name say rLookup

    =OFFSET('8'!$A$1,0,0,COUNTA('8'!$A:$A),4)

    This Lookup table is on a sheet named - 8
    The Table is 4 columns wide

    With the value to be looked up in B6

    =VLOOKUP(B6,rLookup,2,0) gives info in Column 2 of Lookup Table


    =VLOOKUP(B6,rLookup,4,0) gives info in Column 4 of Lookup Table


    Notes.
    - do not have any blanks in your Lookup table
    - the 4th parameter 0 means you want an exact match
    - Do not put any information below Column A of the lookup Table

    Revise Sheet name and references as necessary



    [ This Message was edited by: Dave Patton on 2002-04-03 08:22 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The second workbook that is called in not
    in the same directory as the first workbook,
    so my VLOOKUP is like this:

    =VLOOKUP($C$3,'[Projects.xls]My Project List'!myRange,4,FALSE)

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Below is a sample of the first Workbook

    FY 2001 MY Project List
    PROJ NBR PROJ NAME BR NBR BR CHIEF
    0176 Demographic LAN Support 18 Lau
    0902 Division InterProject 02 Kreilick
    0906 CPS 44 Tucker
    0937 LEHD 04 Prevost


    The above picked up from your edited post shows that you have text fields in your 8 columns of data.

    I'll assume that the sheet that houses your data is called Data, otherwise adjust to suit, and the data start at row 2.

    Activate the option Insert|Name|Define.
    Enter [/b]EndRow[/b] as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(REPT("z",20),Data!$A:$A) [ this replaces the dangerous COUNTA ]

    Activate Add.
    Enter [/b]LTABLE[/b] as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$A$2,0,0,EndRow-1,8)

    [ which defines a lookup table of 8 columns width ]

    Activate OK.

    While the WB/file is open, you can use VLOOKUP in another WB. Supposing that the first one is named/saved as MyFile.xls,

    =VLOOKUP(E1,MyFile.xls!LTABLE,2,0)

    should work.

    Aladin





Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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