Calculated References in Excel

Q: I am trying to copy formulas down a worksheet. I have two rows of data, but when I copy them down I only want their reference to increase by one. For example, in Sheet2 cell B1 references cell Sheet1!A11. When I copy it into the cell two rows down (that is, B3) I want it to reference row 12, but it jumps to 13. Is what I want possible? - Mike McDonald.





A: You can make the row or column in any reference into an absolute reference by prefixing it with a dollar sign ($). When you copy that formula the absolute reference will not change. Without the dollar sign you have a relative reference. If the original formula referred to a cell two rows down and five columns to the right, the copied formula will change to maintain that relationship with the new location.
What you want is neither a relative reference nor an absolute reference; you want a calculated reference. For cell B1 you want to reference the cell from Sheet1 that's ten rows down and one column to the left. For cell B3 you want the cell that's eleven rows down and one column to the left. For each two rows down in Sheet2 you want to go down just one row in sheet 1. The first step is to build a text string that represents the desired reference.
We'll build the reference in R1C1 format rather than the standard A1 format, to avoid the messy requirement of expressing the column using letters. It looks like this: ="Sheet1!R"