I have a bunch of (~100) Excel files, plus one “master” Excel file that is supposed to gather data from all of the other Excel files.
Based upon various websites, I have already found out how to reference cells and cell ranges from other workbooks in a formula. In general, the format must be something like:
path[workbook.xlsx]'worksheet'!cell
Now, it is already quite cumbersome to always put the absolute path there (all of my Excel files reside in the same directory, and I would prefer using relative paths in case I ever decide to move or copy them).
However, what really annoys me is that I cannot enter any formula with such a cross-workbook references without having to go through two (!) UI dialogs:
- First, a warning message about enabling links is displayed. Granted, this can apparently be disabled somehow, even though just globally, not for my specific file.
- Then, an Open File dialog is displayed where I have to select the referenced workbook again.
This makes copying from row to row extremely cumbersome.
Is there a way to prevent especially the prompt to pick the file again?
