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
