Referencing data from other worksheets

NessieMe

New member
Joined
Nov 13, 2012
Messages
3
Reaction score
0
Points
0
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 - View attachment 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!
 
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,
 
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: View attachment 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
 
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.
 
Back
Top