Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel - repeat entries until a fixed number is reached.

Posted on 2016-08-17
Medium Priority
115 Views
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?

0
Question by:Go-Bruins
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 3

LVL 12

Expert Comment

ID: 41759594
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

LVL 12

Assisted Solution

Missus Miss_Sellaneus earned 800 total points
ID: 41759598
Format the cells as Date.
0

Author Comment

ID: 41759651
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

LVL 12

Expert Comment

ID: 41759658
What determines the first date? Is it always 1/1/16? The first date of the current year?
0

LVL 33

Accepted Solution

Subodh Tiwari (Neeraj) earned 1200 total points
ID: 41759691
Try this....

In A5
``````=IF(\$B\$1="","",DATE(2016,1,1))
``````
Change the date as per your requirement.

In A6
``````=IF(OR(A5="",ROWS(A\$6:A6)>\$B\$1-1),"",A5+1)
``````
and copy down.
And of course format the column A as Date.
0

Author Comment

ID: 41759701
Hi,

Assuming B1 is not Zero, the first date in A5 would be today's date + 30 days.
0

LVL 33

Expert Comment

ID: 41759708
In that case, change the formula in A5 with the following one.....

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

Author Closing Comment

ID: 41759743
Worked great! Thank you.
0

LVL 33

Expert Comment

ID: 41759747
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesnâ€™t have any problems. We are bombarded with constant distractions, whether as an overload oâ€¦
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month9 days, 9 hours left to enroll