tonelm54
asked on
Sheet Referencing
Good afternoon,
Ive got one of our users who want to update values from another sheet, the issue is the other sheet in in another folder.
I know to reference another Excel document I can simply use spicify the complete folder and then the sheet, but if I have my workbook in one folder up and then into another, how can I reference that?
I would have thought I could use "'..\My Second folder\[myReferencedWorkbo
So for example:-
Test
+---My First folder
¦ My First Workbook.xls
+---My Second folder
myReferencedWorkbook.xls
Is is possible to do referencing like this, or do I need to use the full path when moving up and down folder paths?
Thank you
Ive got one of our users who want to update values from another sheet, the issue is the other sheet in in another folder.
I know to reference another Excel document I can simply use spicify the complete folder and then the sheet, but if I have my workbook in one folder up and then into another, how can I reference that?
I would have thought I could use "'..\My Second folder\[myReferencedWorkbo
So for example:-
Test
+---My First folder
¦ My First Workbook.xls
+---My Second folder
myReferencedWorkbook.xls
Is is possible to do referencing like this, or do I need to use the full path when moving up and down folder paths?
Thank you
Sorry, you need to use the full path.
Unfortunately, MS Excel doesn't allow relative path referencing. See this old discussion for more detailed explanations.
If you're familiar with Excel Macros and VBA, you could use a VBA routine to get the path of the current worksheet, parse up one level, and append the relative path. Then you could use the Workbook object's .LinkSources and .ChangeLink methods, if necessary, to do the updating easily. It would also be possible, though more clumsily (and usually much slower) to parse the filepath fully and then compose a new formula to inject into worksheet cells, if updating links directly after the workbook has already been opened (and security settings and user intervention possibly having already messed with the links) is not a reliable option.
If you're familiar with Excel Macros and VBA, you could use a VBA routine to get the path of the current worksheet, parse up one level, and append the relative path. Then you could use the Workbook object's .LinkSources and .ChangeLink methods, if necessary, to do the updating easily. It would also be possible, though more clumsily (and usually much slower) to parse the filepath fully and then compose a new formula to inject into worksheet cells, if updating links directly after the workbook has already been opened (and security settings and user intervention possibly having already messed with the links) is not a reliable option.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.