Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro to Reference Column Next to Current Reference
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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: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). -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "dolphinv4" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Circular Problem needs Macro | Excel Discussion (Misc queries) | |||
macro - adding rows to a column that is summed | Excel Discussion (Misc queries) | |||
Macro for Show/Hide Column | Excel Discussion (Misc queries) |