Linking Files in Folders

[bruntonomo]

[FONT=&quot]This might be kind of difficult for me to explain (it would be easier to show), so please bear with me. I am creating an audit sheet (workbook) for each employee that draws data from several other Excel workbooks. I have everything arranged in a folder and sub folders. For example, 2018 has its own folder. Inside 2018 are folders for each quarter of the year (1st, 2nd, 3rd, 4th). Inside the quarter folders are all the Excel workbooks. Hopefully that makes sense.[/FONT]
[FONT=&quot]What I want to do is make it so that I can simply copy the workbooks to one of the other quarter folders and it retains all the functionality/referencing in formulas. However, I do not want it to link to where I copied the workbooks from. I want the links to be relative so that they link to workbooks within the new folders. It would take forever to re-link everything each time I start a new quarter. In web development, it's so easy to create relative links and just move folders wherever, and it will still work. Excel is a major pain because it forces you to create absolute paths.[/FONT]
[FONT=&quot]Any way to do this? Please understand that I am not a hardcore Excel user. I know how to use basic formulas and link between workbooks, format, and other basic things. VBA, macros, and things like this I have no clue about. If I have to do anything like this, please provide step by step instruction so that I know what you're talking about. Thank you![/FONT]

 

[pbornemeier]

Please provide a few formulas with references to other workbooks that would have to be changed.
Do you make use of named ranges in your workbooks? If you are using a named range to refer to external workbooks then changing the reference in the named range will update all formulas that refer to that named range.
The Indirect cell function can also be used to facilitate changing references.
Search and replace can also modify existing formulas to change external references.

This site also discusses some options:
https://stackoverflow.com/questions...lative-reference-to-another-workbook-in-excel