Move cell references over by one column each month

Freshmeadow

New Member
Joined
Jul 23, 2007
Messages
17
Greetings from Guelph.
Each month data for our financial statements is put in one column to the right from the previous month. Is there a procedure we can write to have a formula automatically pick up the new data from the next column to the right for the new month? Right now we copy the "= cell reference" formula manually and past into hundreds of rows to do this.
Any suggestions would be appreciated.
Thank you.javascript:emoticon(':)')
Smile
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
We need more details. Where do you want to copy data from to where and shift column? ARe you summarizing all months data?
Ravi
 

Freshmeadow

New Member
Joined
Jul 23, 2007
Messages
17
Move cell references ove by 1 column each month

Thank you for replying. Currently we do a dump from our accounting software into Excel. Let's say the data for May 2007 is in column G. Previous months are in previous columns. For June, we want formulas to now pick up data from column H and put the data into our income statement. Right now we are using formulas such as "= G1" and " = sum(G15:G20)" and using Edit, Find, Replace to find and replace all references to "G" with "H". Then for July data we will change the references to column I.
Hope this helps.
Thanks again.
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
select col G from 1 to 20 (whatever your range is). and pull the fill handle (plus mark at the bottom right hand of a cell) and drag it to as many columns / months as you want. Formula automatically changes.
Ravi
 

Freshmeadow

New Member
Joined
Jul 23, 2007
Messages
17
Move cell reference over by one column

Thank you! That works! I have been using Excel for 10 years and am now studying Bill Jelen's book "VBA and Macros for Microsoft Excel" but never thought of that trick!
Cheers!(':biggrin:')
 

Forum statistics

Threads
1,181,730
Messages
5,931,703
Members
436,799
Latest member
BasOo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top