+ Reply to Thread
Results 1 to 4 of 4

Cell formula referencing file path contained in separate cell not working

  1. #1
    Registered User
    Join Date
    06-04-2019
    Location
    U.S.
    MS-Off Ver
    2016
    Posts
    48

    Cell formula referencing file path contained in separate cell not working

    Hello again!! This time I have a spreadsheet that contains a lot of data, as well as the workbook and sheet names this data comes from that looks like this:

    [Actual Resolution 2019-05 - New.xlsm]Purposeful'!

    Another cell with the date in the format of yyyy-mm like this:

    2019-05

    Another cell with the Path location like this:

    X:\Actual\Personal\Resolution\

    Another cell that holds a range like this:

    $A$10:$A$69

    and one last cell that only contains a slash like so:

    \

    I concatenate all of this information in a final cell AZ2 that turns it into this:

    'X:\Actual\Personal\Resolution\2019-05[Actual Resolution 2019-05 - New.xlsm]Purposeful'!$A$10:$A$69

    Now I am trying to use this full filepath in a match formula like this:

    =MATCH(H14, AZ2, 1)

    Where the cell H14 contains my lookup value. However, I am getting N/A. Am I not able to use a reference like this in a formula?? Is there any way I could get this to work?? Any help is appreciated. Thank you!!

    Dome

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Cell formula referencing file path contained in separate cell not working

    Try this:

    =MATCH(H14,INDIRECT(AZ2),1)

    I don't think that INDIRECT works on closed workbooks though.

  3. #3
    Registered User
    Join Date
    06-04-2019
    Location
    U.S.
    MS-Off Ver
    2016
    Posts
    48

    Re: Cell formula referencing file path contained in separate cell not working

    Mr. 63falcondude,

    Hello sir!! Thank you for your response!! I don't believe it does either as I'm getting #REF instead of #N/A when I use INDIRECT. Is there any way around this??

    Dome

  4. #4
    Registered User
    Join Date
    06-04-2019
    Location
    U.S.
    MS-Off Ver
    2016
    Posts
    48

    Re: Cell formula referencing file path contained in separate cell not working

    Morning all!!

    I have recently been trying to get this to work using the INDIRECT function as Mr. FalconDude said having the workbook open. However, even though I have the workbook I am trying to refer to open, this formula is still giving me #REF! Any thoughts?? I have verified that the formula does work if I manually type in the entire directory, so I know the file path is correct. Thanks!!

    Dome

    UPDATE: Got it to work, thanks though!!
    Last edited by BrokenDome; 06-17-2019 at 08:23 AM. Reason: Change info

+ 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: 11
    Last Post: 03-01-2019, 01:17 PM
  2. How to change Formula path referencing to the text inside a cell?
    By brianfu777 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-13-2017, 03:51 PM
  3. [SOLVED] Separate complete path to file and directory path
    By drgkt in forum Excel General
    Replies: 19
    Last Post: 10-28-2016, 04:17 PM
  4. Formula to return contents of a cell using file path and cell coordinates
    By gaker10 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 03-18-2015, 09:22 AM
  5. [SOLVED] Referencing File Path - Dynamic Cell Input for Worksheet
    By Chomie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-23-2013, 04:38 PM
  6. Replies: 5
    Last Post: 03-21-2013, 07:34 AM
  7. Formula with cell reference in file path
    By Dave12cd in forum Excel General
    Replies: 1
    Last Post: 08-30-2010, 07:49 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