Macro to Reference Column Next to Current Reference

[dolphinv4]

Hi,

I'd like to automate the following procedure using a
macro. Please help.

1) i have 2 sheets: Sheet1 & Sheet2
2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2!
F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly.
3) Every month when i do my work, i'd have to manually
rekey the formulas to reference the subsequent columns,
ie, Cell B2,D2,F2 will change from above to "=Sheet2!
G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following
month, I have to change manually again to "=Sheet2!
H2", "=Sheet2!M2", "=Sheet2!R2". Is it possible to have a
macro to perform this manually?
4) The above shld apply to the whole column, ie, Cell
B10, D10, F10 should reference "=Sheet2!H10", "=Sheet2!
M10", "=Sheet2!R10".

Thank you!
Val

 

[Héctor Miguel]

hi, dolphinv4 !

... automate the following procedure using a macro
... In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2!F2", "=Sheet2!K2", "=Sheet2!P2"
... Every month when... i'd have to manually rekey... to reference the subsequent columns
... B2,D2,F2... from above to "=Sheet2!G2", "=Sheet2!L2", "=Sheet2!Q2"
... the following month... again to "=Sheet2!H2", "=Sheet2!M2", "=Sheet2!R2" [...]

i see a pattern of 5 columns between each formula, and using a macro you lose the 'undo' stack levels
otho, you could use [i.e.] offset() and a 'step-number' cell [i.e. in 'A1' sheet1] to refer to columns in sheet2
this way, you can always [for/back]ward to 'other' month by changing the 'A1-column_number' [or any other cell]
[b2] =offset(sheet2!$a$1,row()-1,$a$1)
[d2] =offset(sheet2!$a$1,row()-1,$a$1+5)
[f2] =offset(sheet2!$a$1,row()-1,$a$1+10) -> drag/copy down b2,d2,f2...

hth,
hector.

 

[Max]

Perhaps try this set-up ..

In Sheet1
------------
Let's earmark cell A1 for input of a number: 1,2,3, ... 12
to denote the "step-wise" monthly changes required
(Input a "1" in A1 to start with)

Put in

B2 : =IF($A$1="","",OFFSET(Sheet2!F:F,,$A$1-1))
D2 : =IF($A$1="","",OFFSET(Sheet2!K:K,,$A$1-1))
F2 : =IF($A$1="","",OFFSET(Sheet2!P,,$A$1-1))

Copy B2, D2 and F2 down the columns as far as required

With a "1" in A1, you'll find that
B2, D2 and F2 will return corespondingly:
"=Sheet2!F2", "=Sheet2!K2" and "=Sheet2!P2"
and similarly for B3, D3 and F3, etc

Now change the number in A1 to: 2
B2, D2 and F2 will now return corespondingly:
"=Sheet2!G2", "=Sheet2!L2" and "=Sheet2!Q2"
and similarly for B3, D3 and F3, etc

And so on (Just change the number in A1 to suit progressively).