I'm attempting to alter a cell address in a line of formulas that goes on for 36 lines, the formulas reads like this,
='sheet2'!J2
='sheet3'!J2
='sheet4'!J2
I'd like to be able to change J2 to another address without having to manually input it into every formula every time.
Is there a way I can do this?
Use Find and Replace. Select all cells you want to change and hit CTRL+H.
Example below:
Consider replacing your formula with this one.
=INDIRECT(ADDRESS(2,10,1,1,"Sheet"&ROW()))
The point is to have a single formula for all your rows. This is achieved by using the ROW() function which returns the number of the row in which it resides. "Sheet" & Row() will therefore result in Sheet2 in row 2, Sheet3 in row 3 and so on. If you need the Sheet2 in row 5 then you use Row()+3 while INT(ROW()/2) will enable you to use alternating rows. You can modify the above formula to match any pattern of repetition you might need.
The above formula points at J2. ADDRESS(2,10) specifies row 2 and column 10 (column J = column 10). However, you can replace the hard reference with a soft one. Here is a variation of the above taking the column number from cell G1. To change from column J to M you can just enter 10 or 13 in G1.
=INDIRECT(ADDRESS(2,$G$1,1,1,"Sheet"&ROW()))
Note that the $ signs in $G$1 are necessary because you want to copy the formula. But if you would rather enter a column letter in G1 then the variation below will allow you to do that.
=INDIRECT(ADDRESS(2,CODE(UPPER($G$1))-64,1,1,"Sheet"&ROW()))
As you see, every parameter of the ADDRESS() function can be entered directly or using a reference which makes this formula very versatile. And, of course, it's first and foremost advantage is that you don't need a different one in each row of your report. So, it saves maintenance time, too.