+ Reply to Thread
Results 1 to 12 of 12

multiple dependent lists

  1. #1
    Registered User
    Join Date
    03-18-2011
    Location
    Wisconsin, USA
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    6

    multiple dependent lists

    I've looked through this forum and went to several help links and have the basics of the dependent lists. I have office 2008 for mac and was trying to make the worksheet with out Visual Basic macros. I have 5 lists but they are not all dependent of each other. basically the progression would go like this. Goal-exercise-sets-reps-rest. I know how to set up the first two lists but not sure how to get lists 3-5 to interact with the first list selection? Whatever the goal is of the first list will dictate the list for the remaining 4 lists. Any help would be greatly appreciated. I have attached a basic version of what I'm trying to do.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,543

    re: multiple dependent lists

    Hi jbstrong and welcome to the forum,

    I've played a bit with your workbook. See if this helps you log your workouts.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-18-2011
    Location
    Wisconsin, USA
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    6

    Re: multiple dependent lists

    MarvinP,
    thanks for the help. I know this is a basic question but, how do I look at the formula used to create the lists you made? I was thinking of keeping the last three dependent lists separate since they will not have more than 10 to 15 variables but the second list (exercise) will have up to 30 selections depending on the first selection (type). So if I select "strength" I would like that to have a list of exercises separate from Core, ESD, and Power. What functions did you use? INDEX, MATCH, OFFSET, or INDIRECT? I've looked at the help section on contextures and other places but, none seem to quite match up what I want to do. Sorry for all the noobie questions but, I think for what I'm trying to do it's not to advanced. Any more help is greatly appreciated.
    jbstrong

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,543

    Re: multiple dependent lists

    Hi jb
    I realized you don't have VBA on your MAC. I saw you had 5 separate lists and decided it would be better to have a single list. I simply copy and pasted the 5 lists into a single one on the Lists sheet. See Cols A to E. I then moved the data to Col K and deleted Dups for each column. This created a dropdown list for the Main sheet.

    Then on the Main sheet I did Data Validation Lists Dropdowns. No formulas needed!

    I also realized that if you did a COR Type it shouldn't show Exercise of Bicept Curls (Duh). I didn't put that in my example.

    The topic used is Cascading Comboboxes or Cascading Validation Lists, which I don't think you can use without VBA.

    If I was young enough to work out , I'd do your problem like this.
    I'd create a single workout table and put Auto Filter dropdowns at the top. My columns would be.

    Date, Muscle Group, Muscle, Excercise, Weight, Sets, Reps, Rest

    Everytime I did a workout I'd simply add to the bottom of this list.

    If it is formatted at an Autofiltered Table you can easily filter the Muscle Group to see what you did last time and your progress. If you need to see a certain exercise you could also filter it so you could see what you did last time and try to increase weight, sets or reps. You would sort by Date to keep track of your progress for each exercise.

    No VBA is needed. The dropdowns display what is already in the column. When you add a row at the bottom (because it is formatted as a table) it is included in the table. You could build validation lookup for possible choices of your Muscle Groups, Muscles and Excercise if you wanted.

    I hope this helps with keeping track of your workout log. (No Steriods needed )
    Last edited by MarvinP; 03-20-2011 at 12:04 PM.

  5. #5
    Registered User
    Join Date
    03-18-2011
    Location
    Wisconsin, USA
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    6

    Re: multiple dependent lists

    MarvinP,
    thanks for the detailing what you did with the workout sheet. I'm amazed at how easy this must be for you and others on this forum. It's been a long time since I've tried using excel for more than just basic spreadsheets.
    I wasn't intending on using the workbook as a workout log. I was going to use it more to create workouts to make it easier and more efficient with lists that are dependent on the first selection. Although, i think I will use the workbook and info you passed on for myself. There are hundreds of exercises to choose from and I was looking at using excel to break them up into several groups or lists. Although some of the options in each list are identical to other lists I was trying to keep them separate so when I create the full workout spreadsheet I wouldn't have 30 to 40 options. I do realize that with the sets, reps, and rest I could make them generic lists because their are not that many options for them. For exercises I could just make the basic dependent list and use =INDIRECT (A2).
    I did attach another example that might explain it better using groceries. If you or anyone else could look at it I would really appreciate it. I put a note inside the excel file of what I'm trying to accomplish.
    Basically select the type of grocery (fruit,veg, meat, dairy). If you select fruit have a list of all the fruit choices. The last three lists would be quantity to buy, shelf life, and color. These three lists are dependent on the first selection. Some of the lists have identical entries but, i want to keep them separate as the this is only a sample list. I'm not sure if this can be done without VBA or if you can make the lists correspond with just the first selection? IF i can't i can just make the last three lists generic but, wanted to know if it's possible to do what I stated above.
    Thanks,
    JBstrong
    Attached Files Attached Files

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,543

    Re: multiple dependent lists

    Hi jb,

    It is my understanding that filtering the lists of data for the second and third drop down uses events behind the sheet. Unfortunately this is code or VBA. I'm not a MAC guy so I gave you my best shot using big tables and autofilter as these don't need code. You can create lists and use them for Validation lists but to have a selection then filter another dropdown is code.

    I still like the AutoFilter idea. It is almost the same as what you want. You just have this big table and you would click on the top to filter the table. You can use multiple filters on the table and best of all - No Code!!

  7. #7
    Registered User
    Join Date
    03-18-2011
    Location
    Wisconsin, USA
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    6

    Re: multiple dependent lists

    MarvinP,
    Thanks for he help. I will probably go with the table and auto filter for now. Or I can always just set up the other lists independent of the first two. I really just wanted to know if it was possible without code or VBA and thank you for letting me know. It saves me time trying to find the answer online somewhere. I could also save myself the headache and just get the new Office 11 for mac which supports VBA.

    jbstrong

  8. #8
    Forum Contributor Charlie_Howell's Avatar
    Join Date
    09-27-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2007
    Posts
    218

    Re: multiple dependent lists

    try this method and let us know if it works for you.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-18-2011
    Location
    Wisconsin, USA
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    6

    Re: multiple dependent lists

    Charlie_Howell,
    This works great! How complicated is it to set a sheet up like this? If you can't tell I'm still pretty new to making lists interact like this. I looked through the file you made and can see the lists you made but is there a way to see the actual formula? Sorry if I'm missing the obvious answer but I would really like to know what type of lists you used and how you connected them all.

    Thanks,
    JBstrong

  10. #10
    Forum Contributor Charlie_Howell's Avatar
    Join Date
    09-27-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2007
    Posts
    218

    Re: multiple dependent lists

    JB, it's not complicated at all. Just look at the way the lists are cascaded. Every cell with data validation refrences to the one prior to it.Check out the named ranges and look at the names. They are all on the second sheet.As for the data validation, Just open each of the data validation cells as if you were going to create a data validation and look at the tiny ,simple, formula. ie. "=indirect(A1)" You just have to organize your data so that you can refer to every nuance. Every thing in a list will have a sub list. I hope Ive explained this method with clarity. If not, let me know and I'll give it another shot.

  11. #11
    Registered User
    Join Date
    03-18-2011
    Location
    Wisconsin, USA
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    6

    Re: multiple dependent lists

    Charlie Howell,
    After I had more time to look at the lists and Data Validation it all made sense. I had to look up what "VLOOKUP" did and how it worked in excel. I thought it was more complicated than it is. Thanks again for the help!

    JBstrong

  12. #12
    Forum Contributor excelkeechak's Avatar
    Join Date
    07-21-2008
    Location
    India.
    MS-Off Ver
    2010 /2013
    Posts
    352

    Re: multiple dependent lists

    I had a situation a same situation.will post a new thread..
    Thanks
    Last edited by excelkeechak; 04-11-2011 at 04:49 AM.
    THANKS
    ExcelKeechak

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1