Use first day of month formula in a drop down list

[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. 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

 

[BASFMark]

Thanks Biff, it's working fine now.

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





.

 

[T. Valko]

Good deal. Thanks for the feedback!