Forum Discussion
Make External Cell References Relative by Default
Just need to know if this is possible, I have a user asking if the default can be changed and I haven't found a definitive answer yet.
When you're in the formula bar and reference a cell located in another workbook by clicking or copy and paste, it makes that reference in the format of "[exeternal-workbook]Sheet1!$A$10" (the absolute position of the cell being referenced). Is it possible for the default behavior to instead be for the relative position? E.g. "[external-workbook]Sheet1!A10".
My suggestion is Click Another Workbook Reference -> Rename -> Apply 'F4'
It will remove '$' symbol. $A$10 will become A10.
My suggestion is Click Another Workbook Reference -> Rename -> Apply 'F4'
It will remove '$' symbol. $A$10 will become A10.
Logaraj Sekar, I think that is probably the best I can suggest to this user. It saves some clicking, at any rate.
SergeiBaklan, me either, but I promised that I would look into it some. This user is also an executive in our organization, so it required a little extra effort besides saying "it can't be done".
Stephan, the question about default settings for references is quite old, more than 10 years for sure. My guess F4 shortcut was introduced very long ago as partial answer on this question.
However, if you find something (e.g. changing of registry key, or, fortunately, more simple option) please share then.
As for F4 please take into account it works in circular mode, with each click it goes through absolute-two mixed-relative reference changing cycle.
Hi Stephan,
I never heard such Excel default behaviour (relative references within wookbook and absolute ones on external workbook) can be changed.
Hello Stephan
There are only a few situations when Excel uses absolute references as a default.
Besides external workbook references it also happens with cell references when defining a name.
I guess that MS wanted to 'protect' the users from problems caused by relative references in these cases.
And I think there is no setting to change this default behaviour.
