Months

A

adn4n

HI THERE I NEED TO DO A COMBO BOX SO THAT WHEN I CLICK JANAURY (OR AN
OTHER MONTH) THEN A LIST OF DATES ARE SHOWN BENEATH RELEVANT TO TH
MONTH...EG JANUARY ...31 DATES ARE SHOWN FOR THAT MONTH, FEBRUARY....2
DATES ARE SHOWN ETC..., I DON'T KNOW HOW TO DO THIS, CAN YOU PLEAS
INSTRUCT ME IF YOU KNOW, THANKYOU
 
F

Frank Kabel

Hi
first: please turn of your CAPS-LOCK as this makes it difficult to read
your posting and it's also considered as shouting in NGs

To your question. Try the following:
1. Create the drop-down:
- select cell A1
- goto 'Data - Validation' and choose 'List'.
- Enter your month names as data source

2. Create the dates for this month
- select cell A2 and enter the following formula
=DATEVALUE("1-" & A1 & "-" & YEAR(TODAY()))

- select cell A3 and enter the following formula
=IF(A2+1<DATE(YEAR($A$2),MONTH($A$2)+1,1),A2+1,"")

- copy this formula down to A32
 
A

Arvi Laanemets

Hi

Create a sheet p.e. Months

Into Cell A1 on Months, enter the formula
=DATE(YEAR(TODAY()),ROW(A1),1)
, format as Custom "mmmm" and copy down into range A1:A12

Define the range Months!$A$1:$A$12 as named range, p.e. Months

On your sheet, select a cell where you will select month (p.e. A1)
Format the cell as combo, using Data.Validation.List with Source:
=Months
, and format the cell as "mmmm"

Into cell A2 enter the formula
=IF(MONTH($A$1+ROW($A2)-ROW($A$2))<>MONTH($A$1),"",$A$1+ROW($A2)-ROW($A$2))
and format it as "dd"

Copy A2 to range A2:A32. It's done (so long as you remain always by current
year - otherwise you have to create the months list diferently)!
 
B

Bob Phillips

see response in .programming

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Arvi Laanemets said:
Hi

Create a sheet p.e. Months

Into Cell A1 on Months, enter the formula
=DATE(YEAR(TODAY()),ROW(A1),1)
, format as Custom "mmmm" and copy down into range A1:A12

Define the range Months!$A$1:$A$12 as named range, p.e. Months

On your sheet, select a cell where you will select month (p.e. A1)
Format the cell as combo, using Data.Validation.List with Source:
=Months
, and format the cell as "mmmm"

Into cell A2 enter the formula
Click to expand...
=IF(MONTH($A$1+ROW($A2)-ROW($A$2)) said:
and format it as "dd"

Copy A2 to range A2:A32. It's done (so long as you remain always by current
year - otherwise you have to create the months list diferently)!
Click to expand...
 
A

adn4n

Hi there, first of all i am sorry i typed in capitals it wasn't mean
for.

I have one more question sorry.

(Please refer to the file attached)

1) When you click on the 'Timesheet' worksheet you can see that th
timesheet is displayed.

In the combo box there are 20 employee names. I wanted to have eac
employee with their own private timesheet (on the same template) Whe
for example 'Kathy' name is selected from the combo, the figures do no
changed according to the hours she has done. I would like to know ho
to do this, thankyou

Attachment filename: model.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51565
 
A

Arvi Laanemets

Hi

At start, it's a bad idea to add attatchments here - many of us here NEVER
open any attatchments. When there really is a need to look at your table,
then maybe someone asks you to send it per mail - so it can be checked with
antivirus properly before opening it. And mostly a simple explanation of
your data structure, what you want, what you dit so long, and what is wrong
the results you got, is enough. And it forces you to look logically to your
data when your compose your message - sometimes it allows you to find the
solution yourself, before you send the message at all.

About getting rows for some particular employee, firm, or month are
discussed in various Excel NG's sometimes - not very often, but I remember
some. P.e. look for thread "Data with 'headings" at the side ..." in this NG
at March 17. 9:01, or "Filter data by using From and To dates without using
Macros" in microsoft.public.excel.worksheet.functions at Febr. 28. 15:16.
Maybe there are some ideas for you.
 
Top