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,
0
Utf
2/25/2010 12:22:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1400 Views

Similar Articles

[PageSpeed] 23

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


"BASFMark" <BASFMark@discussions.microsoft.com> wrote in message 
news:B3EA59AC-9DF4-4F17-A226-F7495D24A63C@microsoft.com...
> 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, 


0
T
2/25/2010 2:04:10 AM
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 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
> 
> 
> "BASFMark" <BASFMark@discussions.microsoft.com> wrote in message 
> news:B3EA59AC-9DF4-4F17-A226-F7495D24A63C@microsoft.com...
> > 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, 
> 
> 
> .
> 
0
Utf
2/26/2010 3:57:02 PM
Good deal. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"BASFMark" <BASFMark@discussions.microsoft.com> wrote in message 
news:BDAC3BC9-8F77-488B-9F8E-450CDD0190D8@microsoft.com...
> 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 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
>>
>>
>> "BASFMark" <BASFMark@discussions.microsoft.com> wrote in message
>> news:B3EA59AC-9DF4-4F17-A226-F7495D24A63C@microsoft.com...
>> > 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,
>>
>>
>> .
>> 


0
T
2/26/2010 4:28:39 PM
Reply:

Similar Artilces:

Send As permission for mail-enabled list
I've set up a mail-enabled distro list in Exch2003. I've tried both Universal and Global and receive the same results. I have multiple users I need to add to this list and to have Send As permission (i.e. I need for multiple users to be able to send mail from this mail- enabled account). I've been configuring and re- configuring this for about 4 hours now and I've found that I add the group and then add a user and grant that user Send As permission. Any subsequent users added in the same manner cannot send from this account. They receive a message stating that they...

Using Publisher for long reports
Hi. I write long reports (50 pages) in which layout and style are very important. I'm thinking of switching from Word to Publisher since Word's management of pictures and layout drives me wild. Can anyone advise me if Publisher might be the right choice? Does anyone have some long document templates they could show me? -- Giles It sounds like Publisher would be perfect for you since you indication that the layout is important. I can't help with a sample tho because (1) the longest I've done is about 24 pages and (2) my newsletters contain personal contact information and...

How can I use my calendar and other features wo using Outlook emai
I have Windows7 OS. I upgraded to MSO 2007. I cannot access the calendar, etc. without setting up an email profile which my ISP will not allow using the email address. How can I use my calendar and other features without using Outlook email? I could use the calendar on previous versions without setting up an email account. Why can't you use Outlook w/o a mail profile? What happens when you try? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft....

Can I delete OE and just use Microsoft Outlook 2002
I would like to delete Outlook Express is this advisable? I am going to be using Microsoft Outlook 2002 which I recieved w/Toshiba PDA and already have it set up on the same PC as OE. Can I use Microsoft Outlook for multiple e- mail accounts? Differences Between Outlook and Outlook Express http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257824 With Outlook, you can integrate and manage e-mail from multiple e-mail accounts, personal and group calendars, contacts, and tasks. "Delia Marlowe" <anonymous@discussions.microsoft.com> wrote in message news:780901c4025...

Find Next Row With No Value In It
I've got this nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row + 1 Cells(nextrow, 1).Select which will find nextrow with nothing in it and select cell in Col A, but now I've got formulas in the cells, so instead of finding the next row with nothing in it, I want to find the next row with no value - even if it's got a formula in it. Can I do this by amending what's in the what:="*" part of this code? It's always better to include all the parms for .find(). If you don't, then you're at ...

How do I open doc's from Publisher 2000 when I am using Pub 2003?
I recently purchased Publisher 2003 and can't seem to edit any document from a previous version. Are you running Norton AV? Try disabling NAV Script Blocking ~~~~~~~~~~~~~~~~~~~~ Start NAV Click Options. If a menu appears when you click Options, then click NortonAntiVirus. The Norton AntiVirus Options dialog box appears. Click Script Blocking. Uncheck Enable Script Blocking (recommended). Click OK. Restart the computer if prompted to do so. -- JoAnn Paules MVP Microsoft [Publisher] "GAR" <GAR@discussions.microsoft.com> wrote in message news:01222D01-7A00-4...

Referencing .NET 2.0 but end result uses .NET 3.5
I'm using DevStudio 2005 and I have a project that references System.dll ver 2.0.50727 and I accidentally compiled code that used an overload that only exists in version 3.5 because my intellisense showed it. I tried to compile it on another machine that never had the .NET 3.5 install and it wouldn't compile and the intellisense had that version of the overload yet both references looked identical; looking at ver 2.0 of System.dll The method in question was WaitHandle.WaitOne. ..NET 2.0 WaitOne() WaitOne(Int32, Boolean) WaitOne(TimeSpan, Boolean) ..NET 3.5 WaitOne...

DISTRIBUTION LIST #15
When I open one of my distribution list it comes up on a properties menu with the names listed but I can't make any changes. All the other list I have work fine. Any help would be appreciated. ...

You a 7-Day FREE Trial
We Are Offering You a 7-Day FREE Trial to the internet's Hottest New Business Opportunity! If you missed out on the DOT COM boom, now is your chance to cash in on the massive and growing global demand in our $20 BILLION PER YEAR market. We'll show you how to create an income that will come to you for years and grow with each passing month. A SIMPLE, Fully-Automated, REJECTION-FREE Internet Marketing System! Finally, an EASY, proven, FREE system that will truly help anyone willing to follow our guidance succeed! Average People Are Making Money! Average people are putting their home...

WinDbg: Unable to get verifier list
I've been attempting to get to the bottom of a recurring BSOD crash happening on my system. I've already had 4 crashes so far over the past two weeks. So I've identified that NTOSKRNL.EXE is involved in all of them so far. It always somewhere in the stack. So I enabled Driver Verifier on NTOSKRNL, as well as HAL.DLL, NTFS.SYS, and FLTMGR.SYS which were also identified on the stack during various of the events. Okay so I had my latest crash yesterday, and it occurred on NTOSKRNL as well. The Verifier was already enabled on the system prior to this crash, and then wh...

Using a drop down box to select all results
I have a summary page which pulls data together from several sheets an displays summary statistics etc. I want to be able to use a drop down list to select particular product and only show data relating to those products - so far so good. But ..... having put in my drop down and referenced it in my formulae now cannot show the summary data for ALL products. Is there a wildcard that can be used in drop downs to select all -- Message posted from http://www.ExcelForum.com You could add (All) to your list of products, and check for that selection in the formula. For example: =IF(A2=...

using interop to create document word 2007
I am using c# and word interop to open a mailmerge document fill it in and then display it to the user. Once i display it the c# app has nothing more to do with the word document. The user can edit it as needed and then save it as a pdf and that is fine. The issue is I would like to set the default path for the document and it could be a network drive or sharepoint and will change every time also it will never be where the original document was opened. ...

Exchange 2003 Mail between servers stops after a few days
Hi all, I have an odd situation where mail destined for a different administrative group in the same domain stops after a few days; a restart of the server where mails are queing clears the problem. The destination admin group is attached via a connector (smtp), and it's this connector that goes into a retry state under fault conditions. A bridgehead at either end is used, although looking at message tracking suggests the bridge-heads are not being used. Local mail delivery is unaffected, only mail to the remote admin group is affected. There are firewalls involved: the sites are...

drop down boxes #4
Hi everyone, Ok i'll outline the situation. I am trying to produce an invoice page on a system where the user can produce an invoice etc, and I am wanting to make a cell show the total cost for the accomodation. I have a drop down box for the location name and for the cost per day, and the number of days is shown in a separate cell. Is there a way to show the total cost of the accomodation in this single cell? Help needed urgently! Thanks, Andy. Andy I am not sure of what you want but from what you say, all you need is a simple formula in that cell. Say the charge per day is ...

Can I use Free Excel Viewer 97/2000 in Windows XP?
Microsoft downloads doesn't list the free excel viewer compatible with Windows XP. Is there a free download to view and print excel spreadsheets, that's compatible with Windows XP? Linda The Viewer for Office 97/2000 works fine under Windows XP. Gord Dibben Excel MVP On Fri, 22 Oct 2004 15:11:01 -0700, "Linda" <Linda@discussions.microsoft.com> wrote: >Microsoft downloads doesn't list the free excel viewer compatible with >Windows XP. Is there a free download to view and print excel spreadsheets, >that's compatible with Windows XP? Linda An...

using excel from dotnet
situation: I'm working on a new app in vbexpress 2008 using acad com interop Also need to write extracted data to excel. problem: At home(laptop) i have excel 10. At the office excel 12. (both seem to have excel5 listed in refs.) I need to work at both locations. can i reference both 10 and 12 in the same vbproj (i think not) can i just reference 5 and is that just an older version with common (but not the latest) interface? i guess i can just have 2 vbproj files (one on laptop one at work) and just copy the .vb class files that are being edited back and forth, not cop...

creating a program that uses data from a sensor....
Hello, I want to create an MFC program that uses parameter values passed to it from a meter (specifically the resistance in ohms of resisters in an electronic circuit). What are the hardware components that would be needed? And what MFC class(es) would I use to accomplish this? Thanks, RABMissouri2006 On 21 Oct 2006 08:10:50 -0700, "RAB" <rabmissouri@yahoo.com> wrote: >Hello, > >I want to create an MFC program that uses parameter values passed to it >from a meter (specifically the resistance in ohms of resisters in an >electronic circuit). What are the ha...

Drag and Drop Records
Hi, I need to create a relationship between two sets of data, Purchase Order and Production Schedule Order. I've reviewed many different ways to get this done but always come back to manual intevention due to the two sets of data being different. So, what I would like to do is create a form with two sub forms, both in datasheet view. The user drags a record from one datasheet to the other, which then updates the two fields in the table being dropped on. Just wondered, is this possible? and if so got any pointers on how to do it... Thanx in advance Marcus ...

how do you formula's but leave the value.
hi,how do you formula's but leave the value. if a1=1 and b1=2 and you have a code in c1 that says =a1+b1 c1 value would = 3 but how do you then delete the formula so that is stays as 3. i hope you understand what i mean. Hi Craig copy (don't cut) C1 and then click on C1 again and choose Edit / Paste Special now choose values and click Ok this should give you what you want Regards julie "craig" <anonymous@discussions.microsoft.com> wrote in message news:386401c4013b$71f004b0$a601280a@phx.gbl... > hi,how do you formula's but leave the value. > if a1=1 ...

how to use outlook w/o exchange server?
Trying to use outlook and cannot send/receive email. error msg 0x8004010F. I work from home, have no employess, no need to share address book. Just trying to use it w/ a new PDA I have. The exchange server appears to be a fee based service - I don't know. I just want to use Outlook to schedule my appts, keep contacts and use email. SMScott <SMScott@discussions.microsoft.com> wrote: > Trying to use outlook and cannot send/receive email. error msg > 0x8004010F. I work from home, have no employess, no need to share > address book. Just trying to use it w/ a new PDA ...

how do i ignore a number when using MODE function?
Hi I have a list of numbers that I want to find the MODE in: 0 0 1 2 0 0 3 3 2 2 2 0 0 But I want to be able to ignore the Zeros How do i go about doing this so the mode function gives me the #2 only Thank you -- Dev4me ------------------------------------------------------------------------ Dev4me's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29709 View this thread: http://www.excelforum.com/showthread.php?threadid=498091 =MODE(IF(A1:A20<>0,A1:A20)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the...

How do you convert a formula cell to a constant cell
I use a formula to calculate the value, such as time difference, and want to save the value not the formula. The value (constant) will be used later to adjust both time cells used as arguments in the original formula--thus the need to preserve the value computed rather than the formula. After calculation, copy the cell with the formula then Edit - Paste Special - Values on the same cell. HTH "JQLogan" wrote: > I use a formula to calculate the value, such as time difference, and want to > save the value not the formula. The value (constant) will be used later to > ...

Minutes and Seconds Formula
I'm developing a workout programs for runners. I need a spreadsheet that will allow a user to enter min/seconds that is their target for running a mile. Then I need a formula that will provide intervals at every 16th of the mile of what there time should be. Any help. I thought i knew how to format to show min/sec but i'm having all kinds of problems THANKS! Put your target time in A20, and in B20 add =$A$20*COLUMN(A20)/16, then copy across to Q209. I input a target time of 4:32, and got values of 00:17, 00:34,00:52, etc. <vbg> -- HTH RP (remove nothere from the em...

Change the Default Global Address List
Hello, I need to change the default global address list to show only users and contacts from my domain. Is it possible to change the default global address list? Nowadays, The default global address list shows users from my domain and many contacts from others domains. thanks in advance. i have the same situation. i'm trying to figure out how to make another global address list with enternal contacts. if you figure it out, i would love to hear about it:) "Maurit" wrote: > Hello, > > I need to change the default global address list to show only users and >...

drag and drop in notify area
I'm having a lot of trouble figuring out how to implement drag and drop to my application's icon in the notification area. I want to users to be able to drag files to my icon, and my application to capture it and retrieve full path & name of the file. The notify icon doesn't have in it self the drag drop event, because the drag drop to the notification area is captured by the taskbar and not the icon. There closest I've found is a article in the KB (http://support.microsoft.com/default.aspx?scid=kb;en-us;139408), but it describes the opposite action -- dragging an i...