I am trying to copy an absolute cell reference from one sheet to another. The nature of the second sheet means that the formula will move from cell to cell depending on where it is placed. Is there a way to copy an absolute cell reference but change it automatically to be relative to where it is on the page?
For instance, suppose I want
b3 always to reference
c3. In this case, I know it would be
=b4*$c$3 Additionally, when I use a macro to paste this section of the sheet to another, it could be anywhere on the page, so
$c$3 needs to change to reflect that.
Is there any way of doing this without manually changing the formula? I am designing this for a novice to be able to put a number in the quantity box on the sheet, and then have all references thereto change automagically.
(I can provide screenshots if necessary, and if I get the rep to post them.)