Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
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? –  Scott Feb 28 '13 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. –  ian thelwell Feb 28 '13 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). –  Scott Feb 28 '13 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. –  Scott Feb 28 '13 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. –  ian thelwell Mar 1 '13 at 12:13
show 1 more comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
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?) –  Scott Feb 28 '13 at 17:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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