+ Reply to Thread
Results 1 to 17 of 17

I cant work out how to create a drop down menu that relates to another drop down menu

  1. #1
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    I cant work out how to create a drop down menu that relates to another drop down menu

    Can anyone help me link all the sheets please? I have colour coded the boxes so you can see what needs to link to what, I can only have one option in the prime area or specific area not one in each. I haven't added all the development files along the bottom because it would take me forever. I need the empty boxes next to prime area, Specific area, aspect of area, stage and link to development to be drop down menu's

    Can anyone advise me please?
    Last edited by louise2613; 08-18-2012 at 12:27 PM. Reason: changed attachment and wording

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    Hi,

    The data validation lists for "Item" and "Time" on each day are identical, so there's no need to have dependent drop down lists. You can just allocate the Item one to cell C8 and the Time one to C10.

    Maybe I'm completely missing the point...
    If I've been of help, please hit the star

  3. #3
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    I am trying to create something else but because of confidentiality I changed everything, the "actual" work sheet doesn't contain the same info, it just seemed easier to explain! If I can alter the actual worksheet i'll upload it. I did add it in another post this morning but had to delete!

  4. #4
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    I have now attached an ammended document thats a shorter version of what I need to update - due to confidentiality I have had to chop alot out
    Last edited by louise2613; 08-18-2012 at 01:09 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    Aha, well that makes more sense!

    Have a look at the attached. There are notes explaining that the green cell is for.
    This row can be hidden, but NOT deleted.

    EDIT: This time with the attachment - Sorry, me being a doofus!
    Attached Files Attached Files
    Last edited by Spencer101; 08-18-2012 at 12:35 PM.

  6. #6
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    Can you have a look at the last attachment I posted instead for me please? its named help, thats the correct booklet I need to alter with a few alterations

    TIA
    Last edited by louise2613; 08-18-2012 at 12:40 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    Quote Originally Posted by louise2613 View Post
    I have now attached an ammended document thats a shorter version of what I need to update - due to confidentiality I have had to chop alot out
    In this example, the data validation in cell F3 should be =INDIRECT(F1) and the one in F4 should be =INDIRECT(F2)

    Just make sure that your named ranges have correct names. i.e. You can't have LIT in the dropdown in F2 and a named range called LITERATURE and expect it to work. Hope that makes sense..

    And there's no need for the green cell that was in the first example.
    That was only necessary for the weekday/weekend scenario presented.
    Last edited by Spencer101; 08-18-2012 at 12:43 PM.

  8. #8
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    Quote Originally Posted by Spencer101 View Post
    In this example, the data validation in cell F3 should be =INDIRECT(F1) and the one in F4 should be =INDIRECT(F2)

    Just make sure that your named ranges have correct names. i.e. You can't have LIT in the dropdown in F2 and a named range called LITERATURE and expect it to work. Hope that makes sense..

    And there's no need for the green cell that was in the first example.
    That was only necessary for the weekday/weekend scenario presented.
    The highlighted bit I understand but the indirect bit I don't, not very good with excel

  9. #9
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    Indirect is a formula that makes Excel look at the contents of another cell for example.

    So rather than typing the data range name in the data validation box, you use INDIRECT and put the cell you want it to reference to. That way the data validation will be looking at the data range named the same as the current contents of that cell.

    So =INDIRECT(F1) will look at a data range called PSED if that's what's in cell F1, or PD or CL if that's what shows in F1.

    Does that make more sense?

  10. #10
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    I typed =INDIRECT(F1) in F3 and it just dispalys 0

  11. #11
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    You don't enter it directly in the cell, you have to go to "Data / Data Validation" and enter it in the window that pops up.

  12. #12
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    hahaha ok i'll give it a go...thanks!!

    I have attached the original now, because this is what its supposed to do but I cant get F3, F4 and F6 to tally up
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    Are you saying this attached version doesn't work or you need the previous one to work the same as this?

    Because this version appears to work just fine to me....

  14. #14
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    You can only have an item in prime area or one in the specific area not both, the prime area links to everything fine but when I delete contents of prime and use specific area the aspect box doesn't drop down and link to anything and neither does the stage or link to developmeny matters.

    So for example in the specific I want LIT (F2 Observation page), then (F3 Observation page) should drop down so I can pick either Reading or Writing EYFS Lists (G7) & (G8) then (F4 Observation page) should drop down so I can pick a stage (E59-65) or (G59-65) EYFS Lists - named LITReading and I should be able to choose one of them then (F6 Observation page) I should be able to pick from a drop down menu the development matters linking to the stage i.e Enjoys looking at books and other printed material with familiar people. from (B2 Literacy page)

    Does that make sense? the Prime box seems to do what it should

  15. #15
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    Due to the way the workbook has been set up, you will get no drop down list in F3 if either F1 or F2 are blank.
    The reason behind this is the data range F3 looks for a data range called the same as F1 and F2 combined. Therefore if either F1 or F2 are empty, it will not find a valid data range name and so gives you no option.

    If F1 and F2 are both populated, it all appears to work fine.

  16. #16
    Registered User
    Join Date
    07-19-2010
    Location
    Midlands
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    ok thanks for your help!

  17. #17
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: I cant work out how to create a drop down menu that relates to another drop down menu

    No worries.

    If that's got everything sorted please mark the thread as solved.

    Have a good evening.

    S.

+ 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