+ Reply to Thread
Results 1 to 4 of 4

Referring to cells in a second excel document?!?

  1. #1
    Registered User
    Join Date
    06-06-2007
    Posts
    9

    Referring to cells in a second excel document?!?

    I have a huge document called INFO.xlsx which contains a lot of information
    I have a small document called WELCOME.xlsx which uses some of the information from the INFO.xlsx

    From WELCOME I refer to the cells in INFO like this:

    = INDIRECT("'["&Sheet2!B1&".xlsx]INFORMATION'!$C$6")

    (Sheet2!B1 contains the correct filename of INFO)

    If I open WELCOME.xlsx - all the fields give error: "#REF!"
    If I then open the INFO.xlsx - all the fields now show correct data

    Why do I have to open both documents for WELCOME.xlsx to work correctly?
    Isnt there a way to let WELCOME.xlsx refer to cells in INFO.xlsx without having both documents open?
    Attached Images Attached Images

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Referring to cells in a second excel document?!?

    indirect won't work on closed workbooks.
    you can use INDIRECT.EXT if you download and install the free add-in Morefunc.xll

    one question why do you use the indirect since you said you have only one INFO.XLSX
    open both workbook then just a simple link would do.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    06-06-2007
    Posts
    9

    Re: Referring to cells in a second excel document?!?

    Thank you vlady for the answer.

    I dont know how to make it work other than use INDIRECT

    = INDIRECT("'["&Sheet2!B1&".xlsx]INFORMATION'!$C$24")

    If I do without then I get an error... "#REF!"

    = '["&Sheet2!B1&".xlsx]INFORMATION'!$C$24

    Is there a better way to do it?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Referring to cells in a second excel document?!?

    Hi,

    As vlady has said Indirect won't work with closed workbooks. Do as he suggests and create a direct link, i.e.

    =[Info]Sheet2!$B1
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. Replies: 0
    Last Post: 05-28-2014, 04:18 PM
  2. referring to cells in the same row of a formula
    By drossen in forum Excel General
    Replies: 1
    Last Post: 04-07-2010, 05:29 PM
  3. Replies: 2
    Last Post: 01-14-2008, 09:41 PM
  4. Referring to cells
    By april27 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2006, 10:20 AM
  5. referring to other cells with formulas
    By ALex in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2005, 01:06 PM

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