In this tutorial I’ve created a formula that can retrieve a list of items, repeat that list, and automatically adjust if items are added to or deleted from that list.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a list of cities on a tab called “Cities”:

And on a tab called “Repeat” I’ve created a formula to copy that list of cities and keep repeating that list for as many rows as I copy the formula down:

The formula is:

=IF(ISBLANK(INDIRECT(“Cities!A”&ROW(A2))),INDIRECT(“Repeat!A”&(ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2))),Cities!A2)

It used the following functions:

IF

ISBLANK

INDIRECT

ROW

ROWS

COUNTA

Here is a breakdown of the formula:

In English, it is saying the following:

IF the cell referenced with the INDIRECT function (INDIRECT(“Cities!A”&ROW(A2)) ISBLANK, then run this formula: INDIRECT(“Repeat!A”&(ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2))). If it is not blank, then just give me what’s in that cell (Cities!A2).

So, the formula will copy what is in the cells on the “Cities” tab until it hits a blank, then it will run the second INDIRECT formula which will go to the top of the list of the “Cities” tab and return that result. As the formula is continued to be copied down, it will then give the next cell, and the next cell and so on.

If I remove two cities from the list:

My formula will automatically adjust the area where the formula is copied:

Take a look at the video and see the details of how the formula works. This might come in very handy when copying dynamic lists!

**What can you do next?**

Share this post with others that can benefit from it!

Leave a comment or reply below – let me know what you think!

Subscribe to this blog for more great tips in the future!

Check out my YouTube channel – click on the YouTube icon below!

**Free Download!**

Subscribe to Download Your FREE Copy of

"My 70+ Favorite Excel Keyboard Shortcuts" Today!

Hi Michael,

Thanks so much for sharing this tutorial. It’s got me most of the way but I’ve run into a hitch. My scenario is the same as yours apart from the list in “cities” is being dynamically generated by a formula. Unfortunately your formula is also repeating all the “” entries from my dynamic list. Is there a way to get your formula to ignore blank entries?

Thanks!

Dominic, sorry I did not see your comment until today – don’t know why I didn’t get a notification. Anyhow, can you send me your file or a sample and I will gladly take a look at it. Send to mrempel@excel-bytes.com.

Great ! I tried for hours… and fortunately I found your solution!

Would you be able to help me with a problem?

I have 7 Items that I would like to repeat. However, 5 and 6 are relative formulas. My goal is to repeat items 1-4 and 7 but have 5 and 6 be a relative formula dependent on where it is in the repeating sequence. Is this possible?

This is just off the top of my head without testing the concept, but you can try wrapping the formula I used in another IF statement that would test for a formula using ISFORMULA. Then you can insert the formula and use a ROW function to adjust the cell reference to make it relative.

I have actually gone in a different direction. Basically having a helper row that counts to 7 and repeats.

Then nested if statements like =if(A1=1,”BLAH). It seems to be a little easier and allows a little more freedom but i like this idea and will use it for another project.

Great website, long time user, first time commenter. Please never stop.

Have a great day.

Sorry, I lost you at the COUNTA explanation (min. 5:40). If my header is “Species” and I have 31 names under the header in column A, what would the formula look like?

The INDIRECT function concatenates the sheet name (“Repeat!”) along with the column (“A”) to the appropriate row number. To get the appropriate row number, it uses the ROWS function to take the count from cell A2 to whatever cell that formula is at (for example ROWS($A$2:A8) would equal 7) and subtract the number of cities on the cities tab of column A (COUNTA(Cities!A:A) which, for 5 cities would be 6 less 2 – one for the header plus one more which would be 4). So, let’s walk through the math. If I’m looking at the formula in A10, and there are 6 cities, I will want to copy what is in cell A4 (10-6=4). So to get to cell A4, it takes ROWS $A$2:A10 = 9 minus the number of cities and the header of 7 less 2 = 5. 9-5=4, so it will give me cell A4.

This formula works great, I have an additional component that I need assistance with though. I have the repeating list of data in Column B (that I have created with your formula) but now I need to list dates in column A. The dates will repeat until it reaches the end of the other repeating list and then the next day will start. For Example:

11/15/2017 Data1

11/15/2017 Data2

11/15/2017 Data3

11/16/2017 Data1

11/16/2017 Data2

11/16/2017 Data3

11/17/2017 Data1

11/17/2017 Data2

11/17/2017 Data3

How can this be achieved?

Assuming the first row indicated is row 1, enter the first date in cell A1, then in cell A2, enter this formula: =IF(MOD(ROW(A2)-1,SUMPRODUCT(1/COUNTIF($B$1:$B$9,$B$1:$B$9)))=0,A1+1,A1) and copy it down.