I came across this issue several times. Maybe there is an easier way to do this, but I use a work around that works and it takes less time then manually ajusting the data. (depends on the amount of data of course, but when talking hundreds or thousands of rows, deff worth it)
For example, in D1 if you write "A3" and in D2 "A6" and use the fill handle there, it will show "A9" in D3.
What I do then, is to break the formula down into parts and then concatenate it back again using the values from column D.
Then is a matter of copy, paste values. and seach and replace all "=" with "=" to activate the formulas automatically.
See attached
Bookmarks