Use first day of month formula in a drop down list
 

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,

Use first day of month formula in a drop down list
 

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 DataValidationAllowList
Source: =First
OK

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

--
Biff
Microsoft Excel MVP


"BASFMark" wrote in message
...
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,

Use first day of month formula in a drop down list
 

Thanks Biff, it's working fine now.

"T. Valko" wrote:

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 DataValidationAllowList
Source: =First
OK

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

--
Biff
Microsoft Excel MVP


"BASFMark" wrote in message
...
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,


.

Use first day of month formula in a drop down list
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"BASFMark" wrote in message
...
Thanks Biff, it's working fine now.

"T. Valko" wrote:

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 DataValidationAllowList
Source: =First
OK

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

--
Biff
Microsoft Excel MVP


"BASFMark" wrote in message
...
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,


.