Referencing data from other worksheets

[NessieMe]

The title here may be a bit misleading - this is a little more complicated than just referencing data.
I'm trying to reference data from one worksheet to another - that's the easy part.
The data I'd like to output comes from a list on a different worksheet. Is there a function (or group of functions) that looks at what data has already been output to the cell above it, then outputs the next item from the list on the other worksheet? Eventually, the goal is that once the last item on the list is output, the formula would know to start over with the first item.
Here is an example - sample list.xlsx
I've tried looking into manipulating cell positions, but there doesn't seem to be a straight forward way of doing that unless you go to R1C1 format.
Thank you in advance to anyone that can nudge (or shove) me in the right direction!

[Ken Puls]

Hi NessieMe,

What about this? If you're list isn't too long, you could nest it in a CHOOSE() function, then pick the correct position uisng the MOD() function. Based on your example, tossing this in C4, C6, etc (beside your existing items) does work:

=CHOOSE(IF(MOD(COUNTA(B$4:B4),4)=0,4,MOD(COUNTA(B$4:B4),4)),"Red","Green","Blue","Orange")

The key here is that we've provided it four items. If you went to five, you'd need to modify it to:

=CHOOSE(IF(MOD(COUNTA(B$4:B4),5)=0,4,MOD(COUNTA(B$4:B4),5)),"Red","Green","Blue","Orange","Purple")

Hope this helps,

[NessieMe]

Thank you for replying Ken!!
Unfortunately, the list has at least 8 data points and isn't simply a color, but around 6 words (a child's name and the snack assigned to them). It looks more like this: Snack calendar gen.xlsx
I really appreciate your post. I didn't know about Mod or Choose functions. I'm just learning about real programming in Excel.
Thanks again.
Nessie

[peter.abing]

sample list.xlsx
Please see my formula.

[NessieMe]

Thanks Peter!!! I have to rearrange my list a little bit to try this out. VLOOKUP always seemed like a scary function, and I never knew what it did. I hope to have good news tomorrow.