MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How can I automate changing a cell's reference.


Posted by Greg Vincent on March 06, 2001 11:59 AM

Ok, here's what I need to do, but I can't figure it out
for the life of me. I have a row of cells all refering
to cells A1 through Z1 in another sheet. Every week,
I need to update them by one (i.e. first week updated
to A2....Z2,next week to A3...Z3, etc.). How do I do this?
I've been trying to write a macro, but it will update from
1 to 2, but not 2 to 3, or 3 to 4 and so on.


Posted by Mark W. on March 06, 2001 12:21 PM

Don't change the reference...write a "smarter" formula

Greg, for demonstration purposes let's say that
Sheet2!A1:C3 contains...

{1,2,3
;4,5,6
;7,8,9}

...and Sheet1!A1 contains the formula,
=SUM(OFFSET(Sheet2!A1:C1,B1-1,)). Now, you can
enter a row number in cell Sheet1!B1 and this
formula will sum those values on Sheet2. In
effect, you can advance a row in a data set just
by changing 1 value in cell Sheet1!B1. Try it!

Posted by Mark W. on March 06, 2001 12:32 PM

...if you just want to replicate the values...

use:

{=OFFSET(Sheet2!A1,Sheet1!B1-1,,,3)}

Posted by Greg Vincent on March 06, 2001 12:58 PM

thanks Mark. But how would I...

How would I automate this with a macro so that it
will increment the counter by one each time I run
the macro?

Posted by Mark W. on March 06, 2001 1:16 PM

Re: thanks Mark. But how would I...

Just have you macro increment the value in B1.

Posted by Mark W. on March 06, 2001 1:41 PM

...for example...

With Worksheets("Sheet1").Range("B1")
.Value = .Value + 1
End With

Posted by GReg Vincent on March 06, 2001 1:41 PM

Re: thanks Mark. But how would I...

I'm still pretty new at this. I can't get my macro
to increment it by one. Please Help!

Posted by Mark W. on March 06, 2001 2:04 PM

one way...

With Worksheets("Sheet1").Range("B1")
.Value = .Value + 1
End With