When you drag to the left Excel will stay on the same referenced worksheet. Because your reference is locked with the $ when you drag it will still reference J22 no matter what.
If you want to reference a different worksheet you can use indirect. You would need another row which has the correct worksheet names on them and then you could drag to the left. In the code below, the sheet number is the only thing that changes. I have the number 1 in cell A1 and the number 2 in cell A2. When I drag this to the left it will change the reference from sheet1 to sheet2.
Bookmarks