Results 1 to 2 of 2

Thread: vlooup dynamic location

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2024
    Posts
    1

    vlooup dynamic location

    I have several Excel files that are identification cards for suppliers (an Excel file for each supplier) and I want to retrieve the data for all suppliers in one Excel file

    For example

    Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2$,'"["& Range("a2").Value & ".xlsm]sheet1"'!$BC$50:$BD$343,2,FALSE)"


    I want the file name to be changed automatically based on the name of the supplier, based on the “t6” field.

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,439

    Re: vlooup dynamic location

    What, exactly, is in the T6 cell? Is it the supplier name? Do any of your supplier names contain characters that would be illegal for a file name?

    Something like this:

    Code:
    Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2$,'"["& Range("a2").Value & Range("t6").Value & ".xlsm]sheet1"'!$BC$50:$BD$343,2,FALSE)"

Posting Permissions

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



Click Here to Expand Forum to Full Width