+ Reply to Thread
Results 1 to 20 of 20

Formatting area using Data Validating drop downs

  1. #1
    Registered User
    Join Date
    11-23-2007
    Posts
    37

    Formatting area using Data Validating drop downs

    Hello everyone, new user here. I have a question for you all.

    I'm currently developing a calendar that has a list in it with lets say 4 options. What I want the calendar to do is calculate at a specific 'cell' the number of entries that are selected during the month.

    The idea is to have a drop down on each 'day' and a counter that calculates the number of times one specific options has been selected. Once the option has been selected the 'day' will change to the corresponding color.

    I'm sure this can be done but I have no idea how to do it, hence the cry for help.

    I hope this works, sorry for the ambiguous posting before.

    C.

    Here's are two images...
    Attached Images Attached Images
    Last edited by Prax; 11-24-2007 at 11:36 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Prax, the idea of a Thread title is to help others searching for solutions to similar problems, rather like a search with Google, so a title like help is no use at all.

    Without some idea of what you are doing - layout, type of data, I would suggest using SUMIF or a PivotTable.
    Last edited by royUK; 11-24-2007 at 07:34 AM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    11-23-2007
    Posts
    37

    ....

    Thanks for the warm welcome ROY!

    Sorry if I 've infringed on your form.

    Should I post somewhere else under a different topic name?

    P.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Should I post somewhere else under a different topic name?
    No; just edit your original post and change the title to something that would give people a clue as to what you want help with. Then you could add more of an explanation so that someone could have a hope of making a useful suggestion.

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Post the excel file and I'm sure someone will help

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  6. #6
    Registered User
    Join Date
    11-23-2007
    Posts
    37

    ......

    Thank you very much for the reply. I really appreciate the response.

    Here it is.
    Attached Files Attached Files

  7. #7
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Right click say Jan sheet tab and paste in the below

    Please Login or Register  to view this content.
    VBA Noob

  8. #8
    Registered User
    Join Date
    11-23-2007
    Posts
    37

    .......

    OMG! Please forgive my excitement. That works perfect. I will certainly study the code.

    I have one more request. There's a legend on the right. If I could get that to count the number of selections lets say for WTB that would put me into euphoria....lol

    I can't tell you how thankful I am for that help....wow!

    C.
    Last edited by Prax; 11-24-2007 at 12:31 PM.

  9. #9
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    In Cell Q3 enter
    =COUNTIF($A$3:$N$32,P3)
    and drag down

    VBA Noob

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Select cells Q3:Q8 and paste this in the formula bar:

    =COUNTIF($A$3:$N$32, P3)

    Then confirm via Ctrl+Enter to enter it into all the cells at once.
    Last edited by shg; 11-24-2007 at 12:44 PM.

  11. #11
    Registered User
    Join Date
    11-23-2007
    Posts
    37

    ....

    Noob, Shg I can't thank you guys enough. That worked perfectly.

    Again, thanks.

    C.

  12. #12
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Your welcome.

    Please remember to follow forum rules below next time

    VBA Noob

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    With a little additional work, the small calenders on the 2008 tab and the prior month / next month thumbnail pictures could have the same formatting, depending on what version of Excel you're using.

  14. #14
    Registered User
    Join Date
    11-23-2007
    Posts
    37

    ...

    Noob, thank you for the heads up. I will make sure to follow the rules (I've just read them in detail) next time.

    Thank you.

  15. #15
    Registered User
    Join Date
    11-23-2007
    Posts
    37
    Quote Originally Posted by shg
    With a little additional work, the small calenders on the 2008 tab and the prior month / next month thumbnail pictures could have the same formatting, depending on what version of Excel you're using.
    How would I do that? Cause that would be sweet....

    C.

  16. #16
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I'll give you a start, and you can post back with specific questions as necessary.

    In the change event, after changing the format on the month tabs, call another sub, and pass the color index, tab name, and day where the format was applied. In that sub, use the Find method to set the range on the 2008 tab where the tab name is found as part of the month name (e.g., the "Jan" in "January"). Then extend that range to include the whole month. Then find the day number among the month dates, and apply the same color index.

    Hear are a few lines of the code that goes in a code module:

    Please Login or Register  to view this content.
    Also, Noob gave you a great start. Using the worksheet change event requires that you copy the code to each worksheet module. Here's a different way that you would use in the ThisWorkbook module and it handles all the monthly sheets.

    Please Login or Register  to view this content.
    Note that it includes a call to the new sub.
    Last edited by shg; 11-25-2007 at 02:10 AM.

  17. #17
    Registered User
    Join Date
    11-23-2007
    Posts
    37

    ...

    Excellent....I will give it a shot.

    Thank you again, I never expected this kind of help.

    C.

  18. #18
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Good. Get that sorted and we'll talk about the dynamic thumbnails.

  19. #19
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    How are you doing, Prax? Got any part of it working that we can look at?

  20. #20
    Registered User
    Join Date
    11-23-2007
    Posts
    37

    ....

    Shg!! I'm ready to pull out my hair....I cannot get that to work at all, I'm just not fluent enough to get it.....

    I've copied the code to the sheets but when it compiles it's not getting prax Sh.Name?

    C.
    Last edited by Prax; 12-11-2007 at 07:16 PM.

+ 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