Copy two columns across sheet multiple times

Posted on 2012-09-19
Last Modified: 2012-09-22
I have two excel Columns which I wish to copy across

Column b                           Colour C

Jan 2010                           Formula (+A+11)

I want to copy it across so

Column D              Column E                       Column F         Column G
Feb 2010              Formula (+C+10)            Mar 2010          Formula (+E+10)      

and so one!

Any ideas how to do this quickly?
Question by:Mawallace
    LVL 31

    Expert Comment

    by:Rob Henson
    Does it matter what date of the month you have for the months?

    If not, try this, assuming first row in row 3

    For the Feb 2010 put formula  =EOMONTH(B3,1)
    Format with custom format "mmm yyyy"
    For the +10 put formula = C3+10

    Then highlight the two cells and hover over the bottom right corner until it turns to a bold cross. Then drag right as far as you need. It will recognise the "series" and replicate it.

    If the EOMONTH formula doesn't work you will need to enable the AnalysisToolPak add-in.

    EOMONTH(Date,Months) returns the End of Month date for the specified date plus the number of months.

    Rob H
    LVL 11

    Accepted Solution

    put in your values in column A and b and just drag the formula horizontally. on prompt use fill months. your formula will be copied as it is.
    LVL 31

    Expert Comment

    by:Rob Henson
    If you need specific dates in the month use this instead for the date formula:


    The DAY(B4) becomes problematic if you choose 31 and the next month only has 30 days, in this instance it would be better to use EOMONTH.

    The DATE function is actually clever enough to recognise inconsistencies in the increments, ie if you add 31 days to the end of January it will recognise that as the 3rd March rather than the 31 Feb which would be an error. Likewise if you have the month of 13 it will automatically add 1 to the year as well.

    Rob H

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now