[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Copy two columns across sheet multiple times

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?
0
Mawallace
Asked:
Mawallace
  • 2
1 Solution
 
Rob HensonIT & Database AssistantCommented:
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.

Thanks
Rob H
0
 
Swapnil NirmalCommented:
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.
0
 
Rob HensonIT & Database AssistantCommented:
If you need specific dates in the month use this instead for the date formula:

=DATE(YEAR(B4)+IF(MONTH(B4)=12,1,0),IF(MONTH(B4)=12,1,MONTH(B4)+1),DAY(B4))

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.

Thanks
Rob H
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now