Help repeating an alternating membership list

Personally, I would just keep it simple and for rows 2 through 8 (in your picture example) in column C, I would just reference the cell to the left. Then, starting in cell C9 I would reference the top cell (i.e. =C2) and then drag down. But if you want a common formula for column C (adjusted for your stated 12 companies), then in Cell C2:
=INDEX($B$2:$B$13,MOD(ROW()-2,12)+1)
Drag down for your 10 years.

Hi Pauley,

Thanks so much. Is it possible to adjust the formula to account for new companies becoming members and old one dropping out. In total, I have 40 companies over 12 years, so each year the membership composition is different.




Thanks again

Ciaran

Not sure how that would work. How would it know to add/remove? What you could do is whenever the list changes, you copy the names in column C up to the current quarter (e.g. 2018q2) and then paste as values. Then change the names in the list, and the later quarters would update to the new list.

Currently it expects 12 companies, and you could 'fix' that by changing the 12 in the equation above with a COUNTA in column B. But, then, you would also need an equation for column A, since it is also assuming 12 companies each quarter.