+ Reply to Thread
Results 1 to 8 of 8

Use cell range containing doc rather that filepath

  1. #1
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Use cell range containing doc rather that filepath

    Please Login or Register  to view this content.
    The above piece of code has [Database.xlsm] when I would like to use cell ref BA1 (which contains "C:\Users\Scott\Documents\CMC\Database.xlsm") but I can't get the syntax right.

    Can anyone provide some assistance?

  2. #2
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: Use cell range containing doc rather that filepath

    Hi,

    Try this :
    In cell BA1 put C:\Users\Scott\Documents\CMC\[Database.xlsm]BAU'!$E:$Q

    Than use formula like this:
    =VLOOKUP(AF2,INDIRECT("'"&BA1),10,FALSE)

    The lookup file should be open.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Use cell range containing doc rather that filepath

    The problem that you are going to run into is that INDIRECT cannot be used with closed workbooks.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Use cell range containing doc rather that filepath

    Can't do that, also in BA1 I can only insert the filepath not the cell ranges (it is populated by a userform with an
    Please Login or Register  to view this content.
    function.

  5. #5
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Use cell range containing doc rather that filepath

    The add-in below will allow you to use the "=INDIRECT" function with closed workbooks.

    http://www.ashishmathur.com/tag/indirect-ext/

  6. #6
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Use cell range containing doc rather that filepath

    Can it be done without add ins?

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Use cell range containing doc rather that filepath

    I'll bet someone here, not me, could do it using VBA or a UDF. Maybe post in the Programming forum with a link to this thread.

  8. #8
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Use cell range containing doc rather that filepath

    I was trying to avoid the VBA route.

    I'll have a think and see what I can come up with but if anyone thinks of something I can just build into the spreadsheet that would be great.

+ 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. Trim filename from filepath using VBA with range in specific column
    By isritter in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-20-2014, 05:42 PM
  2. [SOLVED] Trying to get the filepath on "Open", but get filepath of a temporary file instead
    By ianpwilliams in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-22-2013, 12:27 AM
  3. [SOLVED] Including VBA code to copy filepath to cell range
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-18-2012, 07:22 AM
  4. [SOLVED] Copying files from a selected filepath to a filepath mentioned in a worksheet's cell
    By subbby in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2012, 03:59 PM
  5. Copy Data Range from Dynamic Filepath
    By sicklysweet in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2009, 02:52 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