MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combobox+Automonth


Posted by Osman on May 07, 2001 7:33 AM

Hi, have two questions in one,
i am trying to make kinda monthlist for me to write and controll what i do every day in month. So i want to have some field with combobox where i can choose between Januar-Decembar, and when i choose the month i want, then it should automatically update whole list like following ... i have columns with days(mo-su) and date (01.xx.2001-31.xx.2001) so the list should be updated like right day to right date. (example, i choose in box May, then first field at date is 01.05.2001 and day is Tuesday second is 02.05.2001 and day is Wednesday and so on, and that should happen auto when i choose month in box as said). Thank you very much


Posted by . on May 07, 2001 8:17 AM

Do you only want to select weekdays that do not end in "y" ?

Posted by Osman on May 07, 2001 8:22 AM

Re: Do you only want to select weekdays that do not end in

well yes, weekdays should be marked as Mo, Tu, We, Th, Fr, Sa and Su, and no i don't want to select them, i want that they be filled automatically by choosing month

Posted by . on May 07, 2001 8:30 AM

Sorry, since your weekdays do not end in "y", I've completely misunderstood your needs!


Posted by Osman on May 07, 2001 8:40 AM

Re: Sorry, since your weekdays do not end in "y", I've completely misunderstood your needs!

well, imagine you have excel list with 2 columns you wanna be autofilled, and you have one combobox which controlls everything. Like this, as said, combox contains only months, from January until December. So when i choose January from combox my two columns with days and date will be autofilled to be correct. Like this under columnd date will be autofilled from 01.01.2001 until 31.01.2001 and in column days autofilled days to be correct, day "Mo" should be given to date "01.01.2001" day "Tu" to date "02.01.2001" and so on. That should also happen with every month i choose from combobox, like October, when i choose it from box, column date will be updated like following from 01.10.2001 until 30.10.2001 and right days will be given to those dates. Huh i really can not explain it better :(

Posted by Joe on May 07, 2001 10:46 AM

Here's a solution (not thoroughly tested) using data validation rather than a combo box. Select cell A1 and choose Data>Validation. On the Settings tab, in the Allow box choose List. In the Source box, list the months separated by commas. Make sure the In-cell dropdown box is checked and choose OK. Fill in other cells as follows:
A2: Date
B2: Day
A3:=VALUE(A1&"-00")
A4:=IF(ISERROR(MONTH(A3+1)),"",IF(MONTH(A3+1)=MONTH(A3),A3+1,""))
B3:=LEFT(TEXT(A3,"ddd"),2)

Copy the formulas in A4 and B3 through row 33. Selecting the desired month in the drop-down in cell A1 should now give you what you're looking for.

Joe

Posted by Osman on May 08, 2001 1:40 AM

Thank you, worked very good