Excel - repeat entries until a fixed number is reached.

Hi all,

I have a fixed number in cell B1.

How do i repeat values (starting at A5) until the fixed value in cell B1 is reached?

Thanks in advance.

 screen
Go-BruinsAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Try this....

In A5
=IF($B$1="","",DATE(2016,1,1))

Open in new window

Change the date as per your requirement.

In A6
=IF(OR(A5="",ROWS(A$6:A6)>$B$1-1),"",A5+1)

Open in new window

and copy down.
And of course format the column A as Date.
0
 
Missus Miss_SellaneusCommented:
Type the first date into A5.

Put this formula into A6.
=IF(A5<A$5+B$1-1,A5+1,"")

 Copy the formula down as far as you may need to go (the maximum number):
0
 
Missus Miss_SellaneusConnect With a Mentor Commented:
Format the cells as Date.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Go-BruinsAuthor Commented:
Thank you, but there is one thing i forgot the mention:

The value in B1 ("Number of times to repeat") could be zero. In this case, there would be nothing in cell A5.

Sorry for the confusion.
0
 
Missus Miss_SellaneusCommented:
What determines the first date? Is it always 1/1/16? The first date of the current year?
0
 
Go-BruinsAuthor Commented:
Hi,

Assuming B1 is not Zero, the first date in A5 would be today's date + 30 days.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In that case, change the formula in A5 with the following one.....

=IF($B$1="","",TODAY()+30)

Open in new window

0
 
Go-BruinsAuthor Commented:
Worked great! Thank you.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
All Courses

From novice to tech pro — start learning today.