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.
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:
It used the following functions:
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!