Use relative reference to change column link??

[Kathryn]

Hi all - I think this should be rather simple.
Unfortunately, I have not been able to figure it out & I'm
getting frustrated! Put simply, this is what I want to do:

I have two spreadsheets (ss1 & ss2). Range("b10:b25") of
ss1 is linked to a column (which contains current week
stats) in ss2. Each week new data is added to ss2,
resulting in the column containing the current week stats
being adjusted one column to the right, i.e., prior week
stats are now in column "AA" and current week stats in
column "AB" - row numbers stay constant.

I would like a macro to open ss1, select range("b10:b25"),
and automatically change the link to ss2 from column "AA"
to column "AB" (maybe using R1C1 reference ??).

Any help would be greatly appreciated

Thx, Kathryn

 

[steve]

Kathryn,

You can do this more simply with a formula:
Put this in B10 of ss1 and fill down to B25
=OFFSET(ss2!B10,0,COUNTA(ss2!$10:$10)-1)

The formula counts the entries in row 10 of ss2 to determine the offset
column. The '-1' is an adjustment to the column count (adjust as needed).
It assumes that row 10 always has an entry...

The formula is self adjusting so no macro or change is needed...

 

[Kathryn]

Steve, thanks for your response. It never occurred to me
to use a formula; your right, it is much easier! I've
adjusted the spreadsheet & it works great.

Thanks again, Kathyn

 

[steve]

Kathryn,

You're welcome!

And one thing you'll find is that using the Offset function, your formulas
won't get messed up if you delete cells...

Happy Exceling...