Need help with data validation format - please !

I'm trying to make this format mandatory when someone enters his/her
data in a cell.

The format would be this : "### ###". Therefor, if the worksheet user
doesn't enter a chain like this one : "113 244", the data just won't
enter. And yes, the space beetween the 3 first and last numbers has to
be there.

I can't seem to find anything in the Data / Validation menu that
enables me to do this :confused:.

Any help would be much appreciated. Thanks in advance.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/2/2003 11:10:45 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
475 Views

Similar Articles

[PageSpeed] 17

This may not be the best workaround, but it seems to work!


Highlight the cells that require this 6-digit formatting.  Click on
Format, Cells.  Then click on Custom.

Enter ### ###

This means that even if a user types in 123456 it will still come out
as 123 456.

Now, with the cells still highlighted, click on Data, Validation. 
Select 'Text Length', Equal To, and type in 6.

Dan.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/2/2003 11:39:16 AM
Reply:

Similar Artilces:

Help with Excel
I have windows xp on my new computer and it don't support excel, i have file on my old computer with excel how can i get excel to work with my xp version You will need to put Excel on your new machine - it is NOT part of Windows. If you have the Excel or MS Office disk from the old machine you can use that to install the applications on the new PC. Of course, once you have it running you must uninstall the apps for the old PC. You can install Excel 97, Excel 2000, Excel 2002 or Excel 2003 on the new PC. If you do not have the disks then you are in for a shopping spree. Best wishes Berna...

Subform record validation
I have a main form for a Case and a subform for entering/updating Payments relating to the case. I want to disallow saving any payment record where the payment amount is zero. I'd prefer that the subform could not be exited where a payment record has a zero amount. The user must have the option of entering an amount or deleting the offending payment record. I've tried various combinations of code in various events and nothing seems to wok. How is this normally done? -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lif...

Validating against XML Schema question.
Hello, I'm trying to use .NET to validate incoming XML documents against the appropriate schema in our database. I do not want to use the document's schemaLocation attribute to validate for various reasons, but rather check against a schema that I pull from a database and add to the reader's schema collection. I am curious as to why the XmlValidatingReader fires an event when the document is invalid, rather than throwing an exception? This makes developing a method to do this a lot harder. Is there some reason it has been implemented this way? Thanks, Adam My guess is this wi...

sorting data in columns
How can I sort multiple columns simultaneously? i.e. I have sixteen columns of numerical data that I want to be able to apply the same Sort to each column individually but I do not want to have to highlight each column separately as this will take considerably longer. Save your workbook first! Try sorting one column, then selecting the next and hit the F4 key. It shouldn't take too long if it works ok. Spiderman wrote: > > How can I sort multiple columns simultaneously? i.e. I have sixteen columns > of numerical data that I want to be able to apply the same Sort to each &...

Save copy of active sheet
I use the code below to save a copy of my Workbook in a specific file path (testing to see if the folders already exist along the way) with a specific, data dependent file name. It works, but I'd like to tweak it a bit and I'm not sure how to write the code. I'd like to save only the active sheet instead of the whole workbook. I'd also like to save only the values and formats to the new file, not the formula and macros. Anyone have any advice on how to accomplish this? Here's my current code: Sub SaveName() If Not Len(Dir("g:\users\one\" & Range("h6&...

What to do?
Hi Everybody? I have problem when I try to build offline data file from Pivot tabel which is connected to Cube on the Analysis Server. Excel return this message: No columns that microsoft excel can use were returned from this query! What to do? ...

help #12
i cannot access my emails ...

Excel charts won't appear or preview. Help!
Hello, In Excell 2003 I cannot get my charts or graphs to appear or preview. I was able too at first. But I accidentally hit right click and then clear several times after having created template line chart for the first time. This subsequently one at a time removed different aspects of my chart until my chart was gone. And now i cannot get the charts to appear again. Not even a preview. Thank you in advance foe your help and advice. This problim has been solved. Thank you. ...

Compare Data
Let's say I want to compare data thusly: I have two columns of numbers, G and N. Column G has more numbers than column N, and includes every number in column N. In each cell in column O is a string associated with the number in column N. I want to comare a number in column N with all the numbers in column G and when I find a match, copy the string in column O to the appropriate cell in column M. I want to repeat this process until all the strings in column O have been matched to their numbers in column G. Whew! So. How do I do this? Thanks, Tommy One way: M1: =VLOOKU...

Charting 3 columns help
I am working on a staffing chart with three columns: Staffing Time Incident 6 3 1234 15 9 1234 I would like a graph to show the "Time" on the left the "Incident" on the bottom and Staffing in the chart I have racked my brain and have not figured out how, to make the chart work that is :o) Do I need to re-arrange the columns? a different way? Any help would be greatly appreciated. Steve In your brief sample, Incident has no variation. How do you want staffing to appear in the cha...

Month view question please
Outlook 2003 Can I get it to show start times in the month view? Thanks... Bob "Bob Newman" <bobnewman@cox.net> wrote in message news:PVPHi.100261$GO6.84541@newsfe21.lga... > Outlook 2003 > > Can I get Outlook 2003 it to show start times of appointments in the month view? > > Thanks... Bob > > "Bob Newman" <bobnewman@cox.net> wrote in message news:0P8Ii.46346$hP1.8443@newsfe13.lga... > > "Bob Newman" <bobnewman@cox.net> wrote in message > news:PVPHi.100261$GO6.84541@newsfe21.lga... >> Outlook 2003 >...

macro needs info on currently highlighted message in search folder
I created a search folder, "Messages Sent Or Received Prior To 2008) by using the Advanced Search and creating a query. The list of messages is shown, including just the lowest level folder name. I can navigate through the messages, and I'd like write a macro to show the full folder path of the currently selected (not opened) message, because I have many dozens of folders and they're not all named uniquely at the lowest level. Can this be done? Thanks tbone Show it where? Outlook version? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Program...

Help with what should be a simple formula..
Hi guys! I'm blanking out on how to write this simple formula, and honestly, not sure how to search the forum for help with this particular one. I'm trying to find profit margins basically. I want to break down profits into 1 Hr, 2 Hrs 8 hrs, etc.. for example, I the time it takes to produce item#1...say 10 Minutes.. In the 1 hr box want to figure out the profit based on 60 minutes of work.. My table looks like this.. A1=Item#1; B1=Cost to make Item; C1=Selling Price; D1=Time to make Item; E1=60 Minute profit; E2=120 Minute Profie.etc.tec.etc. A2=Item#2................................

Formula Help Please?
I'm trying to figure out the best formula to resolve the following: A B C Name Jan. Feb 1 Barb 8 3 2 Group 4 5 3 Ernest 6 7 4 Group 7 9 5 Heidi 9 4 6 Group 4 8 7 Total ____ ______ What we are trying to accomplish, is a formula that totals the values in column "B", however, those values which are "group" (B2, B4 & B6) should be multipl...

help with loading ACT into Outlook2000?
help lol, can't seem to do it any suggestions? thanks Try Act! support? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, anonymous@discussions.microsoft.com asked: | help lol, can't seem to do it any suggestions? thanks ...

CRM 3.0 Installation help...2 errors
Going through the system requirements part of the setup I get a Warning on the IIS part. It cant access the URL-- Error accessing URL http://IRONWOOD-CRM: The remote server returned an error: (500) Internal Server Error. The second error I get is : Setup failed to validate specified Reporting Services Report Server http://ironwood-crm/ReportServer. Error: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. Chris Holub MCP CCNA 1. Check your IIS and default CRM virtual directory is there issue accecing, can you send me detail or s...

DATA DUMP WARNING #2
Maybe the ultimate lesson in this is backup backup backup. Do backups early and often. and use a large cycle - don't write a backup onto the immediately previous media! -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk/support/money/feedback I do not respond to any emails that I have not specifically asked for. <DrWoodardOnDS@hotmail.com> wrote in message news:11095315...

excell formula needed
Hi, In Excell, I'm trying to match a cell (eg H21,with a num value (eg 2530) to a column of figures (M34:M99) that has one match in it and produce a grading A, B, C, D from a column (N34:N99). Anyone have any ideas? Rob. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ =Vlookup(H21,$M$34:$N$99,2,false) Bernard "terrificRob" <terrificRob.uixfy@excelforum-nospam.com> wrote in message news:terrificRob.uixfy@excelforum-nospam.com... > Hi, > In Ex...

Please add attachment functionality to the campaign wizard
When creating a campaign with a quick campaign or a full campaign it would be really handy to be able to add an attachement to an email task, so that on the bulk send the recipiant would recieve a defined liturature sample, currently this is pretty hard to do -- Alistair 2B.net http://www.crmdeployment.co.uk ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in ...

help on composing an aging report for transactions
hello all, I am new to SSRS. I need some help on how to create an aging report in ssrs. This is what my report should look like: Month identify $ $closed $Recovered $Open 0-30day 31-60day Jan 2009 $$$ $$ $$$ $$$ $$$ $$$ I am having trouble on the days column. My user want these fields to contains the total $$ amt that was recovered in 0 - 30days and 31 - 60 days. how to do that? Please help.... Sherry From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-...

Error: "The name could not be resolved. The bookmark is not valid"
I am getting the error as described in the title when I attempt to connect to and my exchange server. 1) Setup One Exchange 2003 organisation (with a back end server in parent domain and one in child domain). Exchange 2003 Forest. A few Exchange 2003 front end servers in DMZ Office 2000 and Office 2003 on Windows 2000 Windows XP clients. ssl OWA and MAPI access (planning to investigate httpsRPC). 2) Problem When I connect with MAPI Outlook clients to child domain Exchange 2003. It comes up with the error: ...

CRM/Help Desk Software
Does Microsoft have a CRM solution comparable to Remedy Help Desk or Avaya. If someone can assist it would be greatly appreciated. CB Hi Carleton, I'm not aware of a Microsoft helpdesk but if you are simply just looking for alternatives, we are using a product called ieSupportManager. If you do a search on Google you will find the website. A free trial version is available which is fully functional. We purchased two copies. We use one for helpdesk / issue tracking and we use the other copy for bug tracking and defect tracking ( although it is primarily a helpdesk product ). There i...

I have MS Office XP Prof. & need System Office pack 2.0 or later
I just recently installed the Windows 7 Operating System on my computer. I use Microsoft Office XP Professional as my office software. While editing a document I received the following message: " The Office Assistant requires Microsoft Agent 2.0 or later. This product is available on the Office System Pack." Where can I get this? The link to request a hotfix is here: <http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=969168&kbln=en-us> The full article is here: <http://support.microsoft.com/kb/969168> hth Sabrina wrote: > &g...

HELP!! Windows Explorer Hangs on Startup!
Hi all, I've had this Presario 2195US laptop for 4-5 years; running XP from SP1 to (now) SP3... no problems. 2-3 weeks ago, I tried to connect a 160gb USB-encased hard drive with no luck... proceeding along I pretty much hosed my windows. Anyway, I decided to reformat/install a copy of XP Pro w/SP3 (thought XP Home didn't like 160gb) and, after a few MS updates, every time I boot the system it shows the DESKTOP, the bottom left of the TaskBar and NOTHING on bottom-right. Cursor moves, I can Ctrl-Alt-Delete and, when I move cursor to TaskBar area... cursor turns in...

Getting data from Access to Excel?
How would you go about pulling data from Access database into Excel? (Like weekly reports) Troy, Data|Get External Data|New Database Query Select MS Access Database The Wizard should take you through the remaining steps of selecting the data you want to import and the criteria (if any) that you wish to use. PC "Troy" <corptkm@yahoo.com> wrote in message news:nLDXa.3726$Jk5.3138269@feed2.centurytel.net... > How would you go about pulling data from Access database into Excel? (Like > weekly reports) > > You can also push-it-out of Access to Excel by (While...