Forum Discussion
Making Relative Links in Excel workbooks
Why does Excel default to absolute links when you link two workbooks. I nearly always want to copy the formula across several cells (and link to range of cells in source workbook) which means I have ...
The truth is I'd never noticed that, and I routinely do make a connection. Anyway, two thoughts short of changing the default--whether or not that's possible, I don't know. Look in the "Preferences" panel.
- It's easy, as you are first establishing the link, to use the F4 key on your keyboard , to cycle through the various absolute/relative references.
- I have begun using the new Dynamic Array function FILTER to import a large range of rows and columns. It takes but one formula (displayed below), and uses the absolute references in the function but gets the entire table (or the set of data that meets the filter selection criterion (or criteria, if there's more than one).
- =FILTER([FiDo.xls]Worksheet1!$A$5:$T$100,LEFT([FiDo.xls]Worksheet1!$A$5:$A$100,1)<>"")