Use first day of month formula in a drop down list

B

BASFMark

I'm using the formula =DATE(YEAR(B4),MONTH(B4),1) where cell B4 = Today()
and this works to give me the 1st day of the current month.

My question is:
How do I use this in a drop down list on a separate worksheet in the same
workbook? I have tried to name the cell containing the 'date' formula, but it
looks like Excel does not allow naming a cell with a formula. Each time I
name the cell (select the cell, click on the 'name box', type name, enter)
when I re-select the cell it hasn't changed the cell name. It defaults back
to the row/column name.

Thanks,
 
T

T. Valko

Works OK for me.

B4: =Today()
B5: =DATE(YEAR(B4),MONTH(B4),1)

The formula in B5 can be reduced to:

=B4-DAY(B4)+1

Select cell B5
Type First into the name box, Hit Enter
Goto Sheet2 select cell A1
Goto Data>Validation>Allow>List
Source: =First
OK

Sheet2 A1 now has a drop down list with the selection of 2/1/2010
 
B

BASFMark

Thanks Biff, it's working fine now.

T. Valko said:
Works OK for me.

B4: =Today()
B5: =DATE(YEAR(B4),MONTH(B4),1)

The formula in B5 can be reduced to:

=B4-DAY(B4)+1

Select cell B5
Type First into the name box, Hit Enter
Goto Sheet2 select cell A1
Goto Data>Validation>Allow>List
Source: =First
OK

Sheet2 A1 now has a drop down list with the selection of 2/1/2010

--
Biff
Microsoft Excel MVP





.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top