Cell Formatting #3

I want c2 to have the first name of an employee preceeded by a comma
automatically....  ,MARY.  The first name will change but the , always
needs to be there.  Is there a way to do that?

And is there a way to force all users of the report to type in capital
letters?


-- 
Tralee6
------------------------------------------------------------------------
Tralee6's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29273
View this thread: http://www.excelforum.com/showthread.php?threadid=490146

0
12/2/2005 1:50:26 PM
excel 39879 articles. 2 followers. Follow

1 Replies
455 Views

Similar Articles

[PageSpeed] 29

On Fri, 2 Dec 2005 07:50:26 -0600, Tralee6
<Tralee6.1zf11m_1133531700.6302@excelforum-nospam.com> wrote:

>
>I want c2 to have the first name of an employee preceeded by a comma
>automatically....  ,MARY.  The first name will change but the , always
>needs to be there.  Is there a way to do that?
>
>And is there a way to force all users of the report to type in capital
>letters?


1.  If that is the only entry in the cell, then:

Format/Cells/Number/Custom Type: ,@

2.  You can use data validation to allow only capital letters:

Data/Validation/Settings
	Allow:	Custom
	Formula:	=EXACT(E1,UPPER(E1))

and add some appropriate explanatory messages.

It might be simpler to use a VBA event-triggered macro to force all entries to
capital letters.

Right click on the sheet tab and select View Code.

Paste the code below into the window that opens:

===========================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range

Set AOI = [a1:z1] 'range where to force all caps

Application.EnableEvents = False


If Not Intersect(Target, AOI) Is Nothing Then
    For Each c In AOI
        c.Value = UCase(c.Value)
        If Len(c.Value) > 0 Then
            c.NumberFormat = ",@"
          Else
            c.NumberFormat = "General"
        End If
    Next c
End If
Application.EnableEvents = True
End Sub
==============================

The code adds the leading comma by adjusting the cell format, which is what you
requested.  It does not actually place a comma in that cell.  If you wish that,
you can easily modify the macro:

---------------------
....
	If Len(c.Value) > 0 And Left(c.Value, 1) <> "," Then
            c.Value = "," & c.Value
        End If
....
---------------------


--ron
0
ronrosenfeld (3122)
12/2/2005 3:03:46 PM
Reply:

Similar Artilces:

cell
Are there any way to add text and a function in the same cell? For example Hello =sum(XX:XX) Try something like ="Hello "&SUM(A1:A10) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "danne" <modig123@hotmail.com> wrote in message news:0a7f01c36335$498056f0$a401280a@phx.gbl... > Are there any way to add text and a function in the same > cell? For example Hello =sum(XX:XX) Sure, for example: ="Hello = "&SUM(A1:B1) For more info, try MVP Debra Dalgleish's &...

Macro or Formula needed to search data in cells
Hi I am looking for a way to create a formula/macro to do the following: My worksheet setup: A1:A30000 C1:C5000 01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27 02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32 03 04 05 06 07 08 09 10 11 12 04 05 06 07 08 09 10 11 12 13 etc Each cell contains a 10 number sequence. The range A1:A30000 is my randomly generated sequence. And C1:5000 is my database of archived sequences. If I wish to check if the combination in cell A1 is anywhere in the range C1:5000. I use the formula. =IF(COU...

Install Fail CRM 3.0
I am having a problem with the CRM 3.0 install, an excerpt from my logfile is listed below. My setup is: The CRM box has the following installed: Windows 2003 Std Server MS SQL 2005 (with SSRS installed) Other Boxes: Another Server runs MS Excahnge Domain Controllers I have tried to install CRM to the local domain (Atlas) and to the OU both give this result. I have IIS running, I can browse to the default web., the SSRS Reports and ReportServer sites OK. I got thru the prerequisites OK. Excerpt of the logfile where it stops: 11:39:30|Verbose| Adding account NT AUTHORITY\NETWORK S...

Restore Question #3
Hello All: Just testing 2003 in a lab environment and have a question. Single server with a single mailbox store. Put some mail in user1 mailbox, backed up exchange and deleted the mail. Did a restore and the mailbox is still empty. I understand the concept of the recovery store to retrieve email from a mailbox store but then what is the point in backing up the store if you cannot do a full restore? It surely doesn't restore mailboxes to their state prior to the backup so what's the point? Am I missing something here? Thanks Are you saying the mailbox doesn't show up in...

Validate the format of a number
I need to determine that an entered serial number is valid. It must check that it is 11 characters and follows the format as follows: a letter, followed by a number, followed by 2 letters, followed by 6 numbers, and ending with a letter. For example, the user enters D7PM234567B and the cell next to it would indicate 'valid' or something similar. If 87PM2345674 was entered, it was indicate 'invalid' next to it or something similar to alert the user it is not in the correct format. Thank you very much in advance. Steve This formula =AND(LEN(A1)=11,CODE(...

Compare cells, update based on Ifs
I need to compare cells to the cell above them and to the right of them. Based on the comparisons I'll need to update the original cell with one of those adjoining cell values. After I finish with one column then I need to repeat the procedure on the column to the LEFT of the original column. I know IF, THEN and ELSE statesments but I don't know VB for Microsoft Office products. Range could be all 65,000+ rows on a workseet Start on ColumnJ, Row2 If ColumnJ, Row2 is Null _ If ColumnK, Row 2 is Not Null _ If ColumnJ, Row 1 is Not Null _ ColumnJ, Row2 Value is ...

If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today! I've found the below formula in one of the posts here, but my brain has completely failed on me and I'm not sure how to adapt it to my requirements. I have a value which is entered by the user after a prompt, that populates in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value is 30/11/09. I have another worksheet, 'Data', which has a column populated with dates (say column B). I'd like a macro that looks in column B on the data sheet, and clears out any dates which are gr...

Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. That's just the way it is .. Instead of : =Sheet2!A1 you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1) Alternatively, we could suppress the display of extraneous zeros in the entire sheet via: Click Tools > Options > View tab > Uncheck "Zero values" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot&g...

XML formatting to HTML...beginner question
Hi, I apologize if I am posting this in the wrong forum. I am creating a .net 2.0 website using vb.net. I have an XML file with the following general format: <LearningObjectives> <lo number = '1'> <Title>First Learning Objective. Click for more details</Title> <details>These are the details for LO 1</details> </lo> <lo number = '2'> <Title>Second Learning Objective. Click for more details</Title> <details>These are the details for LO 2</details> </lo> <lo number = '3'> <Title>Thir...

Outlook Archiving #3
Am running MS Office 2000 on HP Pavilion laptop with Windows Me. Occassionally when entering Outlook, I get a message box that asks "Would you like to Archive now?" In the past, when I click "Yes", all was well. Now, I click Yes, but then get another message box that states "Unable to archive one or more folders". I note that I have a file titled Archive.pst which contains 22MB. How can I clear my machine to archive data from Outlook...if indeed I need to do so...?? Help, please. JWC Hi, If you select from the menu: Tools> Options> Other(tab)...

Multiple Cell Formats
Is it possible for a column to have more than one format, but for those formats to be similar. Eg. I would like to have a multi-format date column. The format can either be Month-Year (Jan-99) or Year only (1999). (I would even like to have something like Jan/Feb-99, but I can live without that.) Any help would be appreciated. Thanks. Hi With true Excel dates in column A e.g. 01 Feb 07, in column B =A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07 or =TEXT(A1,"mmm-yy") =A1 Format>Cells>Number>Custom> yyyy will give 2007 or =TEXT(A1,"yy...

Formatting with CONCATENATE
I would like to use the TODAY() function with a leading word, in this case "Per ". The formula ="Per "&TODAY() produces "Per 38238" and I can't see any way to format the "38238" to make today's date readable. It's easy to do it by using 2 columns, one for "Per" and one for the date, but is there any way to do this in one column? Hi, Try some variation of the following: ="Per "&TEXT(TODAY(),"MM-DD-YY") >-----Original Message----- >I would like to use the TODAY() function with a leading word, in...

Conditional Formatting Text!!
Hi I want to conditionally format some text in a spreadsheet using formula: I have column A1 with: 4a Be 3a To 4c To 4b Be 4c Be 5c Be 5c To 6a Be etc... What I want to do is for all cells which contain "5a Be" "5b Be" 5c Be" "4a Be" "4b be" and "4c Be" to be highlighted in Red - is there a formula to put all of them into one formula - If("5a Be" or "5b Be) etc.. Thanks Kiran "Kiran" <kiran.vithal@gmail.com> wrote in message news:1143053337.964222.63000@t31g2000cwb.googlegroups.com... > Hi > ...

Conditional formating using VBA
Hello I have to format cells (I4:J37) if cells (I43:J76) are: 100%-91% (green) 90%-76% (blue) 75%-50% (yellow) <50% (red) How do I write that in VBA? (I am new to VBA so any help would be appreciated) Many thanks Tracey Hi Tracey, I asume your users will enter a value in the range("I43:J76"). In that case you case use the Worksheet_change(Byval Target as Range function like so: Private Sub Worksheet_Change(ByVal Target As Range) ' Check if only 1 cells value is changed If Target.Cells.Count > 1 Then Exit Sub ' Check if changed c...

microsoft office #3
I just reinstalled microsoft office:mac 2004 on my iBook G4 and now whenever I open any of the programs (word, excel and powerpoint) a window saying "an unexpected error occurred while trying to load the microsoft framework library" pops up. I haven't noticed a change in the functioning of the program apart from that, should I be concerned? Hi Nathalie- Did you run Repair Disk Permissions after reinstalling Office? If not, try that, then log out/in & see if the problem persists. Did you use the Remove Office utility before reinstalling? Why did you reinstall in the firs...

Conditional Formating Help
I would love your help with a conditional formatting goal. I would like to color a cell if it is part of a formula in another cell. Below is an example: A B C 1 12 13 =B1/A1 I then want A1 and B1 colored blue 2 12 13 =B1 I then want A1 and B1 colored blue 3 ...

Is there an add-in that will lock the cells like later versions of Excel?
I'm using 97 and for 99.9% of everything I do I works fine except I can't lock cell format so there can only be data entry. I would be nice if I could do that. Marc Hi Marc, Can you be more specific about what you want and don't want. "Marc" <mcnr(N_O-S_P_A_M)@mindspring.com> wrote in message news:QThPf.1161$sL2.501@newsread2.news.atl.earthlink.net... > I'm using 97 and for 99.9% of everything I do I works fine except I can't > lock cell format so there can only be data entry. I would be nice if I > could do that. > > Marc > > ...

Number of cells that have same values
Imagine there are two columns of cells, each row of two cells are corresponding to each other. How to find out the number of rows where the two corresponding cells have the same value? Assume your Columns are A and B, and your rows go down to 20, Array enter this formula (CTRL+SHIFT+ENTER): =SUM(IF(A1:A20=B1:B20,1,0)) Change the column and row references to fit your data. -- Regards, Dave "Lingyan Hu" wrote: > Imagine there are two columns of cells, each row of two cells are > corresponding to each other. How to find out the number of rows where the two > corres...

How do I lock all cells in Excel except 2 which I need unlocked?
Alex, By default all cells in excel are protected or locked, select the cells you want to unlock and go to format, cells, protection and uncheck locked, the go to tools, protection, and protect sheet, enter a password if you want, now only the cells that you unlocked can be edited. Be aware that this protection is very easy to break, the code to do so can be found very easy, but it will work for most people . If you only need a few locked I would select them all first, Ctrl A, then go to format, cells, protection and uncheck locked, then select the cells you want to lock and go ...

Conditional Formatting
Apologies in advice for this being an easy one, but I seem to be having a dumb day! I have a conditional format in cell M17 which is a 'Use a formula to determine which cells to format' =$M17<$K17 full red This works fine. I am then trying to copy this condition down several hundred lines, but it copies it exactly as =$M17<$K17 rather than changing the row number each time it is copied. How do I copy it so that it changes the line number every time? Take out the $ signs. If my comments have helped please hit Yes. Thanks. "The Rook" wr...

Excel functions #3
Hi. How can i change the "," symbol with the ";" in the syntax of an Excel Function ? For examp. ceiling(A1,3) to be ceiling(A1;3) Thank you. Change in the Regional settings of the control panel, on the Regional Options click the Customize button, that will get you there. -- __________________________________ HTH Bob "nikos" <nikos@discussions.microsoft.com> wrote in message news:B673ECB4-472B-4590-8D10-B35634393DB4@microsoft.com... > Hi. > How can i change the "," symbol with the ";" in the syntax of an Excel > Function...

NewsMaestro Usenet Supertool
NewsMaestro 3.7.5 has been released. It includes support for Windows/Linux/Unix. All the class files are provided. Linux version has been verified with Sun's JVM. On Windows, it runs under both Microsoft and Sun JVM. News Maestro is a power tool for usenet that automatically performs any standard Usenet operation, as specified in various RFSs, individually, or in bulk, on any number of groups and articles, utilizing sophisticated filters to select the articles to be processed from/to archives. New features and bug fixes: Source code is included. Version 3.7.5 * Fixed a bug introduced ...

How to count the occurences of a text string within a cell...
I'm trying to count the number of text-strings, in this case commas, within a cell. For instance: A1(text)=AB,BC,CD,DE,EF If I could count 4 commas, I'd know there were (4)+1 objects in the cell... Thanks in advance. I always research as much as possible before asking the forum. The forum has ALWAYS come through with the answer! --- Message posted from http://www.ExcelForum.com/ Something like: =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) or more generic: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"SOMETEXTHERE",""))/LEN("SOMETEXTHERE") &q...

Double Click Formula Cells
When you double click on a cell and there is a formula in the cell then the precendents are selected. Is there a way to turn this off or automatically go back to the cell you were on without any change to the window and its positioning. What is the VBA for this? Thanks in advance JJ Tools>Options>Edit Check "edit directly in cell" and you won't go to precedents. Application.EditDirectlyInCell = True Gord Dibben Excel MVP On Thu, 16 Sep 2004 13:34:07 +0100, "JJ" <f@o> wrote: >When you double click on a cell and there is a formula in the cell the...

Splitting up text in a cell
I have a worksheet extracted from another program with financial data in it. One of the columns contains information about what money has been spent on. This consists of a category and a subcategory. For example one cell might read "Bills : Electricity" or "Going out : Restaurant". The category and the subcategory are always separated by one space, a colon, then another space. What I'd like to do it separate the category from the subcategory by using a formula that can extract the text either before or after the space-colon-space bit. Having looked at some of Excel'...