+ Reply to Thread
Results 1 to 4 of 4

Alternative of INDIRECT function on CLOSED WORKBOOK - No VBA

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Alternative of INDIRECT function on CLOSED WORKBOOK - No VBA

    Hello,

    I'm trying to get the value of a closed workbook.

    I've done a lot of digging, and I know that INDIRECT function doesn't work with closed workbook.

    Some were even suggesting using a third party add ins. However, due to company policy, I won't be able to install and download it. Even if I could, I won't be able to pass it on to my colleagues as they won't have the 3rd party add ins.

    Some were saying to use INDEX formula. I did try it and it does work. Below is the trial formula which works.

    Please Login or Register  to view this content.
    As you can see, this is static, if I want to make it dynamic, I'll have to change the pretty much from the NetworkFullPath to the SheetName.

    I try to use CONCATENATE, but it seems that it'll always refer to as a string instead of a full path name, workbook name and Sheet Name. Thus, I thought of using INDIRECT to change the string to an actual value.

    But no matter how I try, I always either get a #VALUE! or #REF! error.

    What am I trying to do, is it feasible? In a sense having the dynamic path, workbook and sheet name, where these are located in the same workbook on a different sheet, and to finally able to evaluate and give the result of a closed workbook value.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Alternative of INDIRECT function on CLOSED WORKBOOK - No VBA

    This is not the kind of spreadsheet programming I do much, so I am not sure exactly what is feasible and what is not feasible. If VBA and 3rd party solutions are off the table, I am not optimistic that a solution readily exists for exactly what you are trying to do.

    My first thought with something like this is:

    1) Compile the data from the different sources into a single tab in my open workbook, or into a single workbook.
    2) In my working spreadsheet, I use lookup functions (similar to what you are currently using) to extract data from the single tab/workbook.

    As you have seen, lookup functions (without indirect) readily work on closed workbooks. The basic idea I am going for is to figure out how to convert the INDIRECT() part of your "algorithm" into a lookup function.

    Again, I don't really work with databases, but I sometimes see questions like this and wonder if the real "best" way forward on something like this is to put the effort in up front to compile the data from the disparate sources into a good, single database. You could then take advantage of database query type tools to extract information from the central database.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Alternative of INDIRECT function on CLOSED WORKBOOK - No VBA

    Thanks MrShorty for your input.

    Yes, the thought of compiling the data from different sources into a single tab do comes into mind, but it's not feasible due to the amount of data per 'asset' in close to almost 1 million rows. And since we have 100 of thousands of 'assets', my thought of compiling them into 1 sheet is out of the question.

    I do thought of using VBA too, however, I am trying to avoid via opening the workbook, getting the data and then closing the workbook. As to the executives, it's 'slow' via VBA as they'd like it to be instantaneously updated of charts etc when they select of assets choices. Therefore, the 'instantaneously updated' that I can think of is really using formula.

    This way too, they can look at the formula and make any changes if they want to.

    However, that being said, is there a way to 'compile' all these assets data into a memory so that VBA can just read out of the memory (or is it perhaps dictionary - never use dictionary programming before) so that it'll be instantaneously too?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Alternative of INDIRECT function on CLOSED WORKBOOK - No VBA

    I don't know for sure -- as I said, I don't do database work so it is not my strong point. With 1E6 records per asset, 1E5 assets (making 1E11 records of data and you know how much data per record), I have to think that the best tools for querying that much data will be data base tools. I don't know exactly what that looks like. My only suggestion would be to put something like "how to query a database from Excel" into your favorite internet search engine, and find a tutorial that you can use.

+ 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. Replacment for indirect function; external workbook is closed
    By zeko90 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-07-2015, 07:28 AM
  2. [SOLVED] VLOOKUP from right to left in closed workbook - not possible? Any alternative?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-02-2013, 01:41 AM
  3. INDIRECT function for closed workbook
    By BogdanH in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-14-2013, 02:50 PM
  4. Indirect and Closed Workbook
    By Bulvers in forum Excel General
    Replies: 3
    Last Post: 10-18-2011, 10:13 AM
  5. Updating Indirect with Closed Workbook
    By robgardner15 in forum Excel General
    Replies: 4
    Last Post: 07-08-2011, 07:26 AM
  6. Indirect reference to closed workbook
    By Dingo0z in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-19-2008, 01:20 PM
  7. Indirect formula - Closed workbook
    By RichardBerry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-07-2007, 07:32 AM

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