lebowow2000
New Member
- Joined
- Jul 5, 2015
- Messages
- 1
Hi all,
I am trying to set up a financial accounting system that involves linking a central spreadsheet to various source spreadsheets (salaries, receipts) in different Dropbox folders. This is simple enough when working from my own computer, but the system needs to be accessible and editable from multiple computers. The problem is that the absolute path (absolute paths seem to be needed when going into folders in separate locations) can't update automatically when switching to a new computer, so it has to be done manually for each cell, which clearly isn't feasible when there are hundreds of cells being linked.
I have thought about setting up some sort "INDIRECT" command to update the file path for each spreadsheet depending on the computer being used, like this:
=INDIRECT("'"&LEFT(CELL("filename",A1),SEARCH("PART_TO_CUT_OUT_FROM_CURRENT_FILEPATH",CELL("filename"))-1)&"CORRECT_FILEPATH"&"["&"SOURCE_FILE"&"]"&"SOURCE_SHEET"&"'"&"!"&"E13")
But keep running into a slew of problems, like the link only working when the source file is open, the files taking a long time to load, or not being able to automatically expand the cell "E13" across rows and columns because it is in quotes.
This seems like something that would be a common problem for any small enterprise that uses Dropbox and Excel to track finances. Yet I can't seem to find answers to these questions online. Am I on the right track with the use of an INDIRECT command? Is there a better way to go about this? Or is Excel just not designed to work well with links across different folders?
Many thanks!
I am trying to set up a financial accounting system that involves linking a central spreadsheet to various source spreadsheets (salaries, receipts) in different Dropbox folders. This is simple enough when working from my own computer, but the system needs to be accessible and editable from multiple computers. The problem is that the absolute path (absolute paths seem to be needed when going into folders in separate locations) can't update automatically when switching to a new computer, so it has to be done manually for each cell, which clearly isn't feasible when there are hundreds of cells being linked.
I have thought about setting up some sort "INDIRECT" command to update the file path for each spreadsheet depending on the computer being used, like this:
=INDIRECT("'"&LEFT(CELL("filename",A1),SEARCH("PART_TO_CUT_OUT_FROM_CURRENT_FILEPATH",CELL("filename"))-1)&"CORRECT_FILEPATH"&"["&"SOURCE_FILE"&"]"&"SOURCE_SHEET"&"'"&"!"&"E13")
But keep running into a slew of problems, like the link only working when the source file is open, the files taking a long time to load, or not being able to automatically expand the cell "E13" across rows and columns because it is in quotes.
This seems like something that would be a common problem for any small enterprise that uses Dropbox and Excel to track finances. Yet I can't seem to find answers to these questions online. Am I on the right track with the use of an INDIRECT command? Is there a better way to go about this? Or is Excel just not designed to work well with links across different folders?
Many thanks!
