Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    range substitution (excel 97 sr2)

    =ISERROR(VLOOKUP(A1,$B:$B,1,FALSE))
    in above formula, what is the correct format to replace range $b:$b with something like this c:data[database.xls]sheet1'!(b:[img]/forums/images/smilies/cool.gif[/img].
    what I want is to use the data in column b in unopened file.
    thank you.
    Joe

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: range substitution (excel 97 sr2)

    Try creating a range name in the closed file (this will be referenced in the formula) such as:

    VLOOKUP(C6,'D:Book2.xls'!Your_Range,1,FALSE)

    John

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: range substitution (excel 97 sr2)

    Hi,
    The syntax you want is:
    =ISERROR(VLOOKUP(A1,'c:data[database.xls]sheet1'!$b:$b,1,FALSE))
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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