Good day to all:
The issue I have is this: I have a large number of cells in a model that refer to other cells in the same workbook but on different worksheets. So, for example, in cell C40 on Sheet 1, the formula might be "=Sheet2!$E$350". In cell C41, it might be "=Sheet3!$E$350", and in C42 it might be "=Sheet4!$E$350", and so on. So there is a pattern of referring to the same cell in different sheets. This pattern occurs again and again, so that in the D column, for example, each cell might refer to "=Sheet2!$F$350" and "=Sheet3!$F$350", and so on.
What I need to do is to modify each of these formulas to add cell $E$353 from whatever sheet is referenced in the original formula. So, for example, in cell C40 on Sheet 1, the new formula should read "=Sheet2!$E$350+Sheet2!$E$353", and in cell 41 it should read "=Sheet3!$E$350+Sheet3!$E$353", and so on.
So what I did was to record a macro. I began in the first cell, started the macro recorder, and pressed F2 to activate the formula bar. Then I selected everything but the equal sign and copied it. Then I moved to the end of the formula, typed a plus sign and pasted in the original formula, then modified it to read the correct cell address (since they are all the same). However, instead of actually recording my keystrokes, the macro recorder simply created a one-line macro that copied the formula. So, obviously, recording a macro isn't going to work for this problem. I need a new approach.
I would be grateful for any assistance on this.