1

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.)

6
  • A picture may be worth a thousand words, but, in this case, I believe words are more useful. Can you please try again to explain what you want to have happen if, as in your example, cell B3 is defined to be =B4*$C$3, and you copy that and paste it into cell Q7 on another sheet? Feb 28, 2013 at 0:20
  • A picture would definitely help here so much. If I paste the formula =b3*$c$3 from one sheet from d3 and paste it in to the second sheet, via a macro, in to cell p40 I would need the formula to read =n40*$o$40, I hope this helps to describe it. Feb 28, 2013 at 9:52
  • I believe that there will be no answer to this question that is acceptable to you. By the way, reread your question and see if you can understand why I was confused. You said, “I want B3 always to reference C3.” –– but you don’t; you want it to reference Q40 if it is pasted into P40 (on another sheet). Feb 28, 2013 at 17:15
  • I often write ‘Answers’ that begin, “I can’t answer your question, but I might be able to solve your problem.” I’m not fond of doing that –– partly because it means that I failed, and partly because the question-asker hardly ever likes it. But I’m giving up on your question. Maybe if you took a step back and told us what your problem is, we could solve it. For starters: if you want the formula to reference Q40 if it is pasted into P40, why not just use a relative reference in the first place? // P.S. It would have been nice if you had maintained consistency among the examples. Feb 28, 2013 at 17:16
  • Thanks Scott- I think you are right in that i am a bit unclear, i think that is down to my head being all over the shop about trying to solve the problem. If i had the rep i could put a few pics up to show this more and it would seem a lot easier to someone who knows. Mar 1, 2013 at 12:13

1 Answer 1

0

Perhaps you could try the Offset function?

Offset(reference,rows,cols,height,width)

offset(b3,-1,0,1,1)

There are other ways to do this in VBA too but I can't remember at the moment.

1
  • The OFFSET function is a good idea, but I don’t see how it’s going to answer this question. If you say =OFFSET(B3,0,1,1,1), then it’s a relative reference on Sheet1. And if you say =OFFSET($B$3,0,1,1,1), then it’s an absolute reference when you copy & paste it. (Or am I overlooking something?) Feb 28, 2013 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.