+ Reply to Thread
Results 1 to 12 of 12

How to change values based on selection in drop down menu

  1. #1
    Registered User
    Join Date
    05-27-2016
    Location
    Baltimore, Maryland
    MS-Off Ver
    2017
    Posts
    6

    How to change values based on selection in drop down menu

    Hello Everyone,

    I am trying to make an attendance tracker. I have the calendar up and running.

    I have a drop down list for employees. The issue that I am having is that the calendar won't change based on the employee. I want to be able to select the employee name from the drop down list an have the values changes such as present,absence, etc in the calendar. Any help is appreciated. Thank you everyone!

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,299

    Re: How to change values based on selection in drop down menu

    We need a lot more information, preferably your file. If you select the employee, where are the values (present absence, etc.) supposed to come from to be able to put them in the calendar?

    Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    05-27-2016
    Location
    Baltimore, Maryland
    MS-Off Ver
    2017
    Posts
    6

    Re: How to change values based on selection in drop down menu

    Copy of fiscal-year-calendar.xlsx

    Here's the calendar

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,299

    Re: How to change values based on selection in drop down menu

    I have a feeling there a huge missing piece here that is not designed into this calendar. Can you explain how to use this? That is, you want to be able to select a name from the list and have present, absence, etc. displayed in the calendar for that person. Where do you record that information for each person in the first place? My fear is that you want to use the one sheet here to enter all the data for, let's say, Brady Brown, then select Brandon Emerson and enter all the data for him. Then later go back and select Brady Brown and show all the data that you entered for him originally.

    It's my fear because you have not designed anyplace here to capture and save the data for individual people. I can help with that but this is not a simple question about changing values based on a selection in a dropdown menu--it's a spreadsheet design question. I think that any solution to using one sheet as a data entry form for multiple people is going to require macros.

  5. #5
    Registered User
    Join Date
    05-27-2016
    Location
    Baltimore, Maryland
    MS-Off Ver
    2017
    Posts
    6

    Re: How to change values based on selection in drop down menu

    Yes you hit it right on the nose. I did not know how to word it or describe it.

  6. #6
    Registered User
    Join Date
    05-27-2016
    Location
    Baltimore, Maryland
    MS-Off Ver
    2017
    Posts
    6

    Re: How to change values based on selection in drop down menu

    I would love to learn how to do it. If you can share with a video or any tips, it would greatly appreciated

  7. #7
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,983

    Re: How to change values based on selection in drop down menu

    Take a look at this example
    Just 2 month and 4 people
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,299

    Re: How to change values based on selection in drop down menu

    popipipo, that is a good solution for display but you cannot do data entry on the calendar sheet, you have to use the Data sheet to enter data.

    minky2488, can you describe how you want this to work?

  9. #9
    Registered User
    Join Date
    05-27-2016
    Location
    Baltimore, Maryland
    MS-Off Ver
    2017
    Posts
    6
    Quote Originally Posted by 6StringJazzer View Post
    popipipo, that is a good solution for display but you cannot do data entry on the calendar sheet, you have to use the Data sheet to enter data.

    minky2488, can you describe how you want this to work?
    I want it like you described, I want to be able to insert data into calendar under each consumer name. I want to be able to go back to other consumers and seeing the data etc vs making 15 sheets with the consumers name.

  10. #10
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,983

    Re: How to change values based on selection in drop down menu

    I want to be able to insert data into calendar under each consumer name
    You need vba for that.
    My knowledge of VBA is not large enough in order to solve this

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,299

    Re: How to change values based on selection in drop down menu

    I am looking at a VBA solution and will get back to you later today.

  12. #12
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,299

    Re: How to change values based on selection in drop down menu

    Here is a VBA solution. This expands on the idea set up by popipipo, showing the same list of names on the Data sheet, adding the daily codes for all days in the year.

    This required some changes to your existing format and formulas but it should end up looking the same. I also fixed a couple of problems. You can select week display to start on either Sunday or Monday, and I added conditional formatting to shade Sat and Sun no matter which view you select. There was conditional formatting for each month name for no particular reason so I removed that. I made the spacing between months uniform to make it easier to code.

    As before, nothing prevents the user from selecting a letter (P, O, V, X) on a day that is blank for that day of the month. It will be ignored when data is stored and retrieved.

    The code is somewhat sensitive to changes on the worksheet. If you insert or delete rows or columns it will probably cause problems. If you move key cells, like the dropdown with a person's name, it will cause problems.

    This is a programming task, not something that is easy to explain with a few tips or a video. It's custom programming. If you open the file then hit ALT-F11 you can see the code in sheet 2018.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Locking a formula in a column where the values change based on drop down menu
    By uhm_81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2014, 09:57 PM
  2. Formula based on drop down menu selection
    By danjpalmer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2014, 03:45 PM
  3. Replies: 1
    Last Post: 03-27-2013, 07:04 AM
  4. Replies: 3
    Last Post: 01-31-2013, 06:22 PM
  5. Hide a row based on a drop down menu selection
    By AMechEng in forum Excel General
    Replies: 4
    Last Post: 06-20-2012, 03:57 AM
  6. Changing values based on drop down menu selection
    By henro8 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2010, 03:47 AM
  7. change cell values based on drop down box selection
    By Aniruddha in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2006, 05:30 PM

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