Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Formula Copying Automaticly

0

Dear Team,

I want to display months in the column according to the value I gave in another column. suppose, if I give 5 in column A1 then I want to show next 5 months with a year in the columns. What will be the formula

Regards

Ruveen

A B C 5   Jun-23     Jul-23     Aug-23     Sep-23     Oct-23

Answer
Discuss

Discussion

Ruveen. Please do me the courtesy of responding to the Answer I provided to you. 
John_Ru (rep: 6102) May 25, '23 at 5:38 pm
Add to Discussion

Answers

0

Hi Ruveen and welcome to the Forum.

If you have a recent version of Excel (365, 2021, web or mobile- your profile doesn't say) you could create an array formula in one cell which will spill into cells below (which must be empty but you'll want to format as dates).

In the attached file, orange-shaded cell R3 (only) has this array formula:

=EOMONTH(TODAY(),SEQUENCE(P3))

where the 7 in P3 is used with SEQUENCE to produces an array {1:2:3:4:5:6:7} which is used by EOMONTH (end of month) to calculate the last day of the month 1, 2 and 3 (etc.) months after today's date so (currently) spills the following dates into R3 and below:

Jun-23

Jul-23

Aug-23

Sep-23

Oct-23

Nov-23

Dec-23

Change P3 and you'll get more or fewer dates.

Hope this helps- if so, please remember to mark this Answer as Selected.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login