Populate Cells based on Month Selected

[tacnola]

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!!

[Pete_UK]

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

[tacnola]

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.

[Pete_UK]

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

[Pete_UK]

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

[Pete_UK]

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

[tacnola]

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!!

[tacnola]

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!!

[Pete_UK]

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

[tacnola]

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!!!

[Pete_UK]

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

[tacnola]

Awesome!! Atlanta is a GREAT City!! I hope you are able to visit it.

[tacnola]

Good Day Pete....on this same spread sheet, is there any way to "hide" the columns that have days after the end of the month (or starting the next month.).
I have tried all the conditional formatting I can think of. and none of them are working.

Thank you for your help!!

[Pete_UK]

You can't hide columns using conditional formatting. The file that I sent in Post #9 does have conditional formatting which "greys" out the columns after the end of the month chosen in Y1 (for months with less than 31 days), but that is about all you can achieve.

Hope this helps.

Pete

[tacnola]

Yes...perfect!! That was what I was wanting...the greying out of the column! Awesome! Thank you!!

[Pete_UK]

You're welcome. Happy New Year.

Pete