+ Reply to Thread
Results 1 to 14 of 14

How to absolute reference external file path in formula

  1. #1
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    How to absolute reference external file path in formula

    I have a search formula that refers to another open workbook. When other similar workbooks are opened my formula changes and the wrong directory path is substituted. I have used excel file/options/advanced/when calculating this workbook and removed ticks to (1) update links to other documents & (2) save external links. Neither of these solutions are 100% successful.
    My formula fails to work if the path reference is changed. Is there a solution?

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: How to absolute reference external file path in formula

    Can you post what the correct formula is and what it's changing to? Also, can you let us know the names of the files you're using (the one with the problem and the ones which you open which cause the problem)? Finally, are all the workbooks in the same folder? Knowing some of that might help us to narrow down some possibilities - I can't guarantee anything, but we can try...
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: How to absolute reference external file path in formula

    sorry for the delay but have been away.
    "=IF(ISNUMBER(SEARCH('[V6.xlsm]Create Permit'!$V$18,B2)),MAX($A$1:A1)+1,0)"
    the file Create Permit is renamed and saved in alternative directory. When one of these saved files is opened i.e F:permits\HH Permits....
    ('[V6.xlsm]Create Permit'!$V$18,B2) I need this to remain a constant. And if this file is not available a plus would be a default drop down list. Is either the first or second options feasible? I cannot send the files over at this time.

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: How to absolute reference external file path in formula

    Is this what's happening?
    - you have a file called 'V6.xlsm' with a sheet called 'Create Permit'
    - you have another file which uses the formula you've given, referring to that V6 file
    - you open the V6 file and save it in another directory with another name
    - your formula in the second file changes to refer to that renamed&moved file
    - you want the formula still to refer to a file called V6.xlsm in the original location

    If that's the case, then the best thing to do is not to resave/rename the V6 file from within Excel, but instead to close it then copy it to the new location and rename it there (using Windows, not Excel). That way, the second file won't 'know' that the file has been moved/renamed as this wasn't done in Excel, so the formula should stay the same (i.e. referring to the original location and file name).

    Regarding your second point, I think it may be possible, looking at this page: http://answers.microsoft.com/en-us/o...1-a4c5d3ea87fa , but exactly how to do it will depend on your exact requirements.

  5. #5
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: How to absolute reference external file path in formula

    My Start Permit.xlsm when opened immediately opens V6.xlsm, my start permit workbook contains all my reference data 'a central repository' which can be amended. A worksheet called data is critical to all my work.
    When V6.xlsm cells are populated and ready for Save As the critical formula changes in Data from "=IF(ISNUMBER(SEARCH('[V6.xlsm]Create Permit'!$V$18,B2)),MAX($A$1:A1)+1,0)" to "=IF(ISNUMBER(SEARCH('[NewSavedFileName.xlsm]Create Permit'!$V$18,B2)),MAX($A$1:A1)+1,0)" I am trying to stop this happening I hope a forum member can help me with this.

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: How to absolute reference external file path in formula

    If the reference in the formula is always the same, then you could store this in another cell and use INDIRECT to refer to it.

    In another cell (let's say Z1), put this: '[V6.xlsm]Create Permit'!$V$18 (just as text, with no = sign before it)

    In the cell containing the current formula, put this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If the cell reference in V6.xlsm will change, then it's possible to join different text together with Indirect, so you could use something like this (with Z1 containing just '[V6.xlsm]Create Permit'!):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    which would allow you to change the $V$18 cell reference in the formula (if you have similar formulae in various cells).

    Is that something you can work with?

  7. #7
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: How to absolute reference external file path in formula

    thanks for the suggestion but this is not a suitable solution I have numerous cells in column 'A' that use this formula that relates to the search of characters from V18 in column 'B'. Another solution please

  8. #8
    Registered User
    Join Date
    09-27-2015
    Location
    Rhode Island, USA
    MS-Off Ver
    2013
    Posts
    35

    Re: How to absolute reference external file path in formula

    Bob,

    I think Aardigspok's solution is reasonable - it ensures that the file path name remains the same. Unsure why it wouldn't work?

    If it's because you have multiple workbooks addresses, then have a place with those addresses in another section of your workbook and link the indirect function to the respective cells with the appropriate workbook addresses.
    Last edited by Mr_HiB; 09-27-2015 at 01:57 PM.

  9. #9
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: How to absolute reference external file path in formula

    Bob,

    If your concern is that you have so many formulae that changing them will take a long time, then you can use Find and Replace. For example, Find 'RCH('[V6.xlsm]Create Permit'!$V$18,' and Replace 'RCH(INDIRECT(Z1),' or similar (adding the RCH( at the start ensures you won't accidentally replace other formulae you don't want to affect.)

    As Mr_HiB said above, if there are a number of different workbooks/cell addresses you need to refer to, you can use Indirect with references to different cells.
    If you're unsure how to do this and can't upload the file (for confidentiality reasons or similar) then if you can let us know the different formulae / workbook references / cell references which are affected, we can give some more help with the Indirect formulae you'd need.


    However, if you really don't want to do this, then the only other solution I can think of is to use Find and Replace when the incorrect 'updated' filename change happens. i.e. when your formula changes from ...[V6.xlsm]... to ...[new saved filename.xlsm]..., do a Find and Replace to change it back. How effective this will be (and how time-intensive) depends on how often this happens - if it's happening multiple times because of multiple cells, with multiple different filenames, then I would personally go with the 'Indirect' solution, but it's up to you.

    Hope that's of some help.

  10. #10
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: How to absolute reference external file path in formula

    I have two workbooks in C:\Permits\ V6.xlsm and Start Permit.xlsm hopefully a solution can be found. Text characters are typed in to V18 V6.xlsm and from the drop down lists the search in Start Permit.xlsm column B. I used the INDIRECT option but this failed to work. I've uploaded my edited files for additional help
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: How to absolute reference external file path in formula

    I'm at work at the moment and can't download .xlsm files, but I'll try to take a look at home tonight.

  12. #12
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: How to absolute reference external file path in formula

    I had to play around with the links a bit to get them to work after download, but as far as I can tell, using INDIRECT does work.

    I put this in cell Q1 of 'Start Permit.xlsm': [V6.xlsm]Create Permit'!$V$18
    (i.e. the part of the formula inside the Search bracket but not including the first apostrophe or the end ',B2' bit) (note that Excel will probably put an apostrophe in front of this in Q1, but ignore that).
    I then put this formula in A2 and copied it down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As it's a bit difficult to read, here's what's in the brackets after the Indirect: a double quotation mark " then an apostrophe ' then another double quotation mark " then an ampersand & then $Q$1.

    When I tested it by selecting 'Albone R' in V18 on V6.xlsm, I got all zeroes in column A of 'Start Permit.xlsm' apart from in cell A4 (beside 'Albone R' in B4), where there is a '1', which is exactly what I got before amending the formula with the Indirect.

    I can't see any reason why this shouldn't work on your files on your computer/network as well - but please let me/us know if you continue to have problems.
    Last edited by Aardigspook; 09-29-2015 at 05:36 PM. Reason: Add comment about Excel auto-adding an apostrophe

  13. #13
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: How to absolute reference external file path in formula

    Thanks Aardigspook, I chose to copy your solution and not type it in. Works a treat. Now this $V$18 could be the search cell for many workbooks using the same template that have been save with different file names. Can VBA use this named cell($V$18) in any open workbook from directory 'Permits' to provide the same functionality i.e. type text characters and provide a search and the result made available as a data list for selection to populate $V$18? or even a search window that will populate $V$18 with the selection?

  14. #14
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: How to absolute reference external file path in formula

    Glad I could help.

    Regarding your follow-up questions, I'm afraid I'm not particularly good with VBA, so although I think the answers will probably be 'yes', I'm not able to say how. I suggest that you open a new thread in the VBA forum (http://www.excelforum.com/excel-programming-vba-macros/) explaining what you want to accomplish (you can include a link to this thread to save repeating yourself). Good luck.

    ps Thanks for the rep.

+ 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. Extract text from an external Excel using the file path only
    By gaker10 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2015, 12:30 PM
  2. Replies: 0
    Last Post: 05-08-2014, 11:42 AM
  3. Replies: 0
    Last Post: 09-23-2013, 05:59 PM
  4. Formula with cell reference in file path
    By Dave12cd in forum Excel General
    Replies: 1
    Last Post: 08-30-2010, 07:49 AM
  5. dynamic path to reference external cell
    By litleboy in forum Excel General
    Replies: 0
    Last Post: 03-30-2009, 12:06 PM
  6. Replies: 1
    Last Post: 08-18-2005, 01:05 PM
  7. Changing external path and file name
    By chemaster in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-06-2005, 10:27 AM

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