+ Reply to Thread
Results 1 to 11 of 11

Populate Cells based on Month Selected

  1. #1
    Registered User
    Join Date
    03-06-2005
    Location
    Atlanta, GA
    Posts
    52

    Populate Cells based on Month Selected

    I really did search, but I'm not sure I knew the correct terms to search for. I hope this is a simple formula that someone can assist me with! Thank you for any help!

    In Cell Y1 (merged to AB) the user will select the month from a drop down. (Also, the year is selected from AC1 (merged to AE))

    I would like that if a 31 month is selected from this drop down then the #31 will populate in Cell AF2.

    Also if Feb is selected it will blank out Cells AE2-AG2. I'm still trying to figure out FEB LY. If Excel doesn't already have this in a calculation somewhere I might just include Feb Leap Year in the drop down.

    Again,

    Thank you for all your help!!
    Last edited by tacnola; Yesterday at 02:33 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,799

    Re: Populate Cells based on Month Selected

    It would help if you attached a sample Excel workbook, and details of how to do that are given in the yellow banner at the top of the screen.

    It would help us to answer questions like:

    How is the month shown? The full name for the month, or a 3-letter abbreviation, or even a month number (or some other way)?
    Is the year in AC1 shown as a full-century year (4-digit), or just the last 2-digits?
    If 31 goes into AF2, why would you want to blank out AE2 to AG2?
    Are you populating other cells with consecutive dates based on that month and year?

    There are quite a few other questions I can think of, but seeing your workbook would probably answer all of them and avoid guesswork. We might also be able to suggest other ways of doing things that you might not have thought of.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-06-2005
    Location
    Atlanta, GA
    Posts
    52

    Re: Populate Cells based on Month Selected

    I Have attached a sample of my spreadsheet for your review. It is used as a scheduler. I have instructions on the functions of the sheet.

    If you notice on the Lists tab, I have the instructions for the form. I would like to eliminate several of the "manual" choices if possible.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,799

    Re: Populate Cells based on Month Selected

    OK, I think I know what you are trying to do. With your month in Y1 and year in AC1, I assume that you have a formula like this:

    =DATEVALUE(1&$Y$1&$AC$1)

    in B2 to generate the date for the first of that month, and then this formula in C2:

    =B2+1

    which can be copied across to generate all the dates for that month. That formula is fine up to 28th of the month (cell AC2), but then we need to change it to account for February and the 30-day months. Use this formula in AD2:

    =IF(AC2="","",IF(MONTH(AC2+1)=MONTH(AC2),AC2+1,""))

    and then copy that into AE2 and AF2, then your calendar days will automatically adjust to suit the month chosen in Y1.

    Hope this helps.

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,799

    Re: Populate Cells based on Month Selected

    I was composing my reply while you submitted yours, so here's the file that I worked on to deduce what you were trying to do. Perhaps you can amend yours along the same lines.

    I've adjusted it to start in column C rather than B, so my comments above should be shifted over by one column.

    You should apply a custom format of d to the dates, to show only the day.

    If I have chance before I go out, I'll amend yours in the same way. (My computer is very slow at the moment, as it is doing its backups).

    Hope this helps.

    Pete
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,799

    Re: Populate Cells based on Month Selected

    I've made the changes to rows 2 and 4 of your file, so it now responds automatically to changes in the Month and Year on the top row.

    I'm going out shortly, but if you have any other queries I'll pick them up later on.

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-06-2005
    Location
    Atlanta, GA
    Posts
    52

    Re: Populate Cells based on Month Selected

    WOW, This is amazing!! Thank you so much. this is very helpful!! I will play around and let you know if there are any issues!!
    I know you may be gone already! But this is wonderful!!

    Have a good night!!

  8. #8
    Registered User
    Join Date
    03-06-2005
    Location
    Atlanta, GA
    Posts
    52

    Re: Populate Cells based on Month Selected

    One last thing if I might, and I know you might be gone already, but I will eagerly await your response.

    Can you add an "IF ERROR" msg when the cell is blank or has a #Value! error. I would like to be able to keep the Master Sheet Blank therefore not showing the value error when the Month and YR are not selected!

    Thank you again!!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,799

    Re: Populate Cells based on Month Selected

    The formula in C2 needs to be:

    =IFERROR(IF($AC$1="","",DATEVALUE(1&$Y$1&$AC$1)),"")

    and D2 now needs to become:

    =IF(C2="","",C2+1)

    and this should be copied into cells E2:AD2, so if either the year or the month is missing (or both) then all the date cells on row 2 will appear as blanks. I've set this up for you in the attached file.

    In answer to your earlier question about 1st Feb 2020 appearing blank (now edited out), this is caused by your conditional formatting rules clashing. Select cell C2 then click on Conditional Formatting | Manage Rules and you will see that there are 6 rules set up, and these are executed in the order shown. You could delete the second one as it is basically the same as the first one, and both concern the date cells being blank so don't apply in this case. The third rule sets a red background and a bold foreground if a cell on the third row contains "H", which C3 does in this case. However, the 5th rule sets a yellow background and a red foreground if the cells on row 4 contain "Sa", which is also the case here. The red background from rule 3 trumps the yellow background of rule 5, but because you haven't explicitly set the foreground colour in rule 3, then rule 5 gives it a red foreground, and so the number effectively disappears. Consequently, I have amended the rule so it uses a black foreground (rather than be set "automatically"), and this clears the problem. I think the H there is from 1st January 2020, which wasn't a Saturday and so the problem is not likely to occur in the real file anyway, as holidays do not usually happen on a weekend.

    Incidentally, you could combine rules 5 and 6 (which are for "Sa" and "Su") by having a formula which looks at the first letter and sees if it is an "S", i.e. =LEFT(C$4)="S" - that will reduce the number of rules to 4 if you also delete rule 2.

    I've also applied custom formatting of d to row 39, to tidy that row up.

    Hope this helps.

    Pete

    P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can show your appreciation and directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-06-2005
    Location
    Atlanta, GA
    Posts
    52

    Re: Populate Cells based on Month Selected

    Thank you SOOO much! You have help me out greatly! This is a huge time saver for me all of my co-workers!!
    And I have learn something new! My new best Excel friend Mr. "=LEFT()"

    BIG hugs......until we meet again!!!

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,799

    Re: Populate Cells based on Month Selected

    I'm glad to help - feel free to post again if you have any other queries.

    Pete

    P.S. My sister in law has a house in Atlanta

+ 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