+ Reply to Thread
Results 1 to 3 of 3

Referencing closed workbook to get cell values

  1. #1
    Registered User
    Join Date
    02-11-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    1

    Referencing closed workbook to get cell values

    New here. Have searched the site but could not find a satisfactory answer.

    I have the path of the directory in Cell A1 = C:\Sundeep\ I have the file name in Cell B1 = ibm

    Now what I want is the value of cell C6 on sheet data in the file C:\Sundeep[ibm.xlsm]data!C6 and want to put it in cell C1 in the current workbook. I don't want to use vba code and I don't want to use indirect (as that requires the ibm.xlsm to be open).

    If I put this string in C1 (hardcoded), then it works ='C:\sundeep[ibm.xlsm]data'!$C$6

    However, if I try something like this =CONCATENATE("'",A1,"[",B1,".xlsm]data'!")$c$6 it does not work.

    Any help is appreciated.

  2. #2
    Registered User
    Join Date
    11-14-2014
    Location
    Estonia
    MS-Off Ver
    2013 Office for Mac
    Posts
    31

    Re: Referencing closed workbook to get cell values

    IMO concatenate creates a text string and that's why it's not working, as excel is not recognizing it as a "data source", but just thinks that it's any other sentence.

    Wouldn't something like this work?

    A1='C:\sundeep
    A2=ibm

    =A1&[A2.xlsm]data'!$C$6

    Most likely not. Other options are yeah, VBA, or indirect. I've also read about evaluate formula.

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Referencing closed workbook to get cell values

    There is an old add-in called Morefunc.xll that has a function called INDIRECT.EXT that works just like the built-in INDIRECT function except that the source file does not need to be open.

    However, I don't know if this add-in works in newer versions of Excel. It appears you're using Excel 2013 (based on your profile).

    Here's a link to the add-in (it's free!):

    http://www.ashishmathur.com/tag/morefunc/
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Referencing a closed workbook
    By blacktrek in forum Excel General
    Replies: 6
    Last Post: 05-25-2015, 02:52 PM
  2. Sumifs used referencing a closed workbook
    By kgallo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2015, 06:57 PM
  3. Matching data from closed workbook with open workbook based on cell values
    By kbkrueger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-14-2015, 07:42 PM
  4. [SOLVED] Trouble copying data from closed workbook into active workbook, referencing help
    By lepperga in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2013, 01:48 PM
  5. [SOLVED] Referencing a closed workbook
    By RowanB in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2012, 05:13 AM
  6. Copy cell values from open workbook to closed workbook
    By merrener in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2012, 03:14 AM
  7. Cell Values from a Closed Workbook
    By JK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2006, 01:35 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