+ Reply to Thread
Results 1 to 6 of 6

Get data from other workbook with named range

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    House
    MS-Off Ver
    Excel 2003
    Posts
    3

    Get data from other workbook with named range

    I'm trying to pull in data from another workbook using dynamically named ranges. I've done some searches but can't find any examples of what I'm trying to do.

    Workbook_A has several tabs - each with a drop-down list that is used to select the week-end date. When a week-end date is selected a macro dynamically creates a named range based on the week. (For example, if 11/03/12 is selected a named range within that sheet is created with a name of 'Week45'.) I also place the name of that sheet's named-range in cell C4.

    Workbook_B also has several tabs - and also has a drop-down list that is used to select the week-end date. (In this workbook I also place the week (in this example 'Week45') in cell C4.)

    I could create a VLookup formula in Workbook_B to pull data from Workbook_A, but I need it to be dynamic based on the week-end date.

    Is there a way to dynamically pull data from Workbook_A into Workbook_B based on the named range which is in cell C4? I've tried using the VLookup formula with an embedded INDIRECT formula but haven't had any luck.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Get data from other workbook with named range

    INDIRECT does not work with closed workbooks, so you must ensure that Workbook_A is open in the same instance as Workbook_B.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-06-2012
    Location
    House
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Get data from other workbook with named range

    Both workbooks are open so that shouldn't be a problem.

    Here are a couple formulas I tried with no luck...

    =VLOOKUP(A11,INDIRECT('[2013 weekly.xls]WkEnd_Dates'!$C$4),28,0)

    =VLOOKUP(A11,INDIRECT("2013 weekly.xls!"&C4),28,0)

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Get data from other workbook with named range

    The second parameter of VLOOKUP should define the range of your table where you are trying to find data, and the third parameter should define the column number within that table where you want to get corresponding data from. So, with a value of 28 in both cases (and assuming you are looking for a match within column C), your table should extend at least to column AD. It is usually more than just one row deep (assume to row 30). Your second example does not have the correct syntax, as you need '[filename.xls]sheet name'!, and with INDIRECT the cell/range reference should be a string so both of them fail on that as well.

    I would hazard a guess that your formula should be:

    =VLOOKUP(A11,INDIRECT("'[2013 weekly.xls]WkEnd_Dates'!$C$4:$AD$30"),28,0)

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    12-06-2012
    Location
    House
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Get data from other workbook with named range

    Pete - I appreciate the advice - but I'm still stuck...

    It appears I can't use a VLOOKUP formula since I can't hard-code the sheet name into the formula. The sheet name is going to be different each week (each week has it's own sheet in each workbook). To get around the issue of not being able to use a sheet name I dynamically create a Named Range based on the week-end date selected in the drop-down list. (E.g. If the user selects 11/03/12 from the drop-down list a macro does a VLOOKUP to get a Week # and then names a range the same as that week #. For example, when a user selects 11/03/12 the macro names the range in that sheet WEEK45)

    On Workbook_B I'm trying to pull in data from Workbook_A based on the same week-end date. Needs to be dynamic since the sheet will be different based on the selection.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Get data from other workbook with named range

    I'm not really sure what you are saying, but you can include variables within the string used by the INDIRECT function. For example, suppose you have the filename (plus extension) in cell X1 and the sheet name in Y1, then you could do this:

    =VLOOKUP(A11,INDIRECT("'["&$X$1&"]"&$Y$1&"'!$C$4:$AD$30"),28,0)

    so you just need to arrange for Y1 to change in order to get data from a different sheet.

    Hope this helps.

    Pete

+ 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