+ Reply to Thread
Results 1 to 8 of 8

INDIRECT using two workbooks

  1. #1
    Registered User
    Join Date
    12-05-2020
    Location
    Preston England
    MS-Off Ver
    2019
    Posts
    4

    INDIRECT using two workbooks

    I am new and not use to forums... so if I am outside the remit just let me know.

    My issue is how to use INDIRECT accessing information in one workbook to generate a table in another.

    Within a single workbook

    I use the formula =IFERROR(VLOOKUP($C7,INDIRECT("'"&D$6&"'!"&"Q19:R36"),2,0),0) to seek out the relevant worksheet found in D$6
    To create unique lists from several columns an array formula such as =INDIRECT(TEXT(MIN(IF(($Y$7:$AN$26<>"")*(COUNTIF($AQ$6:AQ6,$Y$7:$AN$26)=0),ROW($7:$26)*100+COLUMN($Y:$AN),7^8)),"R0C00"),)&"".

    I have several spreadsheets which use individual sheets for data based on standard template. Some of the spreadsheets have the same data sheets in them.

    My idea is to build one workbook with all the data sheets in one place and for individual spreadsheets to call off the data as required.... which makes sense and should have been my first plan!

    Basically this is to allow me to update the data sheets in one place and not have to copy the same data to replicated sheets.... or worst have different data in the same sheet.

    I can build a dynamic list of sheet names using =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) but I am losing the thread on how to change my formulas to make it work across two workbooks {both open}.


    Any suggestions or alternatives would be very welcome.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: INDIRECT using two workbooks

    Can you post the 2 workbooks for us to see the problem?

  3. #3
    Registered User
    Join Date
    12-05-2020
    Location
    Preston England
    MS-Off Ver
    2019
    Posts
    4

    Re: INDIRECT using two workbooks

    This sample shows how I am doing it on single spreadsheet.

    Tried separate workbooks... just failing to build the formulas correctly.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-05-2020
    Location
    Preston England
    MS-Off Ver
    2019
    Posts
    4

    Re: INDIRECT using two workbooks

    Sample of the two workbook version... formula having trouble with are highlighted
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: INDIRECT using two workbooks

    In Sample 2.xlsx, Overview!D6 contains First State Japan, and Overview!D12 contains

    =IFERROR(VLOOKUP($C12,INDIRECT("'"&D$6&"'!"&"Q6:R15"),2,0),0)

    which displays nothing because the VLOOKUP call produces an error, so the formula return 0, and the workbook's options suppress display of zero values.

    When I change Overview!D6 to [Sample 2 data.xlsx]First State Japan, Overview!D12 then displays 100.0.

    The point here is that external references need to have the form '[workbook_filename.extension]worksheet_name'!range_address . That is, the workbook's base filename with extension inside square brackets, immediately followed by the worksheet name within that workbook, both combined in single quotes when necessary (but ALWAYS safe to include them, so better to do so), then an exclamation point, and finally the address of a range of cells (possibly a single cell). You can't leave out the workbook filename. If you don't want it in Overview!D6, then put it in some other cell, ideally enclosed in square brackets, say in cell X2, then change the formula above to

    =IFERROR(VLOOKUP($C12,INDIRECT("'"&$X$2&D$6&"'!"&"Q6:R15"),2,0),0)

  6. #6
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: INDIRECT using two workbooks

    First, be sure to open the two workbooks at the same time (Sample 2.xlsx , Sample 2 data.xlsx‎)

    Sample 2.xlsx‎ workbook ,worksheet name "Overview" , D7 cell formular , Copy Drag down and accross

    =IFERROR(VLOOKUP($C7,INDIRECT("'[Sample 2 data.xlsx]"&D$6&"'!Q6:R15"),2,),)


    Sample 2.xlsx‎ workbook ,worksheet name "Overview" , K7 cell formular , Copy Drag down and accross

    =IFERROR(VLOOKUP($J7,INDIRECT("'[Sample 2 data.xlsx]"&K$6&"'!M6:N16"),2,),)

  7. #7
    Registered User
    Join Date
    12-05-2020
    Location
    Preston England
    MS-Off Ver
    2019
    Posts
    4

    Re: INDIRECT using two workbooks

    Thank you so much...... both solutions work perfectly.


    Kind of knew I could change D6 to include the sheet name in [] and it would work but just did not grasp where to include in the formula.

    Now to remember how to populate the table automatically from the unique names dynamitic list.

  8. #8
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: INDIRECT using two workbooks

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Using indirect in a hlookup between workbooks
    By rpcoates1956 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-08-2016, 02:59 PM
  2. using indirect across workbooks
    By robgardner15 in forum Excel General
    Replies: 1
    Last Post: 07-08-2011, 03:36 AM
  3. Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  4. [SOLVED] Using INDIRECT to refer to different workbooks
    By Govind in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. [SOLVED] Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. [SOLVED] Using INDIRECT to refer to different workbooks
    By Ken Cobler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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