Results 1 to 5 of 5

Thread: Referencing data from other worksheets

  1. #1

    Referencing data from other worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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

  4. #4
    sample list.xlsx
    Please see my formula.

  5. #5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •