Re: Record Duplication & Formatting Advise Please.....

Hi Dermot

Paste Special is an option under the Edit menu. Instead of just Copy then Paste, you need to copy then Edit>Paste Special> choose Values as this will change the formulae into the absolutes of what the formulae are doing i.e. the Proper names.
When I said delete column B, that was because I assumed the data was in A, and the formula I gave was to go in B1 and be copied down.

It sounds as though you have Account in A, and Name in B.
You can use any column for carrying out the Proper formula.
Use column Z for example, and since the data you want to use is in column B, enter in Z1 =PROPER(B1) and copy down.
Copy the whole of column Z, move your cursor to B1 and Edit>Paste Special>Values. Then you can delete column Z (if you want) or if it is way off your range then it won't matter if you leave it (other than taking up unnecessary space in your work***.

Regards

Roger Govier


Dermot wrote: 
Thanks for the reply Roger,
and the link, cheers.

I have been struggling with this!
When you say "Paste Special" what do you mean?

Quote: "Delete Column B".....is this not the column with the "Proper" function in it?
The other thin I don't under stand is if I put the "Proper" function in an adjacent cell....does this not take up the space that I might want to use for other information.
My column s are Account |Full Name|Address , for example, so if I want to format full name "Proper"....how can I enter the account or Address Name, or do I have to insert another column and hide it?

Another question suggestion please......
I want to be able to enter "y" and "Yes" to appear in a cell or n and "No" to appear.
I tried using "Auto correct" but it seems to work randomly in some cells and not in others......what would be he correct way to do this?
Can you give me an example?






"Roger Govier" wrote:


Hi Dermot

The Proper() function cannot be used in the cell with the entry itself. If the entry is in A1, then in B1 =PROPER(A1) will do what you want.
in A1 roger govier, B1 would return Roger Govier.
Copy down the length of the column to include all of your data.
Then copy the whole of the range in column B, place your cursor on A1 and Paste Special>Values and this will make all of column A the way you want it.
You can then delete column B.

I would do this on a COPY of your data to begin with, just in case!!!

As far as preventing Duplicates, you can use Data Validation, mark the range of cells you wish to validate Data>Validation>drop down to choose Custom and in the white pane type =COUNTIF(A:A,A1)<2
Change the range according to the column you are using.

For more help on Data Validation (and a whole heap more) take a look at Debra Dalgleish's site
http://www.contextures.com/xlDataVal01.html



Regards

Roger Govier


Dermot wrote:

I would be most appreciative if anyone could answer my questions below, thanks Dermot.

I am looking for the best way to configure a column so that if I enter "yes" it enters "Yes".....or if I enter "y" it enters "Yes". At the moment I am using auto correct but I wondered if this is the correct use of this feature and whether or not there is a better way.

I also looked at the "Proper " format, but can't seem to get it to work. Looking up the help feature doesn't help. The help example appears to be for one cell only I would like say all first names and second names to start with capitals. I can't seem to set this up. I end up with either the formula showing in one cell or a circular error. I don't think I have to format each cell individually in a column?

How do I prevent individuals form entering "Duplicate" Rows (records) into my spread sheets ALL suggestions and any links to an example would be appreciated.

Can you advise me what the "Easiest On The Eye" text formating to use in a spreadsheat? I have a few to tidy up and they are uncomfortable to view. I have formatted them to look reasonably good but would be interested in professional tips to improve them further.....any suggestions or links of any tchnical level would be appreciciated

Any other suggestions how I can get to grips with the formatting ffeatures would be appreciated.

Thanks agian for your time
Dermot


.

Quantcast