Record Duplication & Formatting Advise Please.....

[Guest]

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

 

[Roger Govier]

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

 

[Guest]

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]

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 worksheet.

Regards

Roger Govier

 

[Guest]

Roger,
Thanks for the thorough explanation,
Much appreciated
Regards
Dermot

 

[Guest]

Hi Roger,
I thought I had it worked out but I can't seem to get the formatting to copy
to the full column....please advise what you mean by the following
quote...........
Quote:-
and copy down.
Copy the whole of column Z, move your cursor to B1 and Edit>Paste
Special>Values.

I don't think I am doing the copy process correctly. I seem to be able to
only get the formatting in one cell! Can you explain the procedure copy
down.....I have tried all sorts of attempts at the copy and paste.
Thanks
Dermot

 

[Roger Govier]

Hi Dermot

There are various ways of copying a formula to others cells.

Perhaps the easiest way is to use the "fill handle".
When you have your formula in cell Z1, hover the cursor over the bottom
right corner of the cell, until the cursor changes to a small solid black cross.
When you see that, hold down the left mouse button as you drag down the
column as far as you need. When you release the mouse button, the formula
will be copied down through each of the cells, and it will alter accordingly
to =Proper(A2), =Proper(A3) etc. as you go down each row.

Alternatively, you can copy the formula in Z1, either by pressing the Copy
button on the toolbar, or right clicking the mouse and choosing Copy.
Then mark the range of cells you want to copy to by marking the first cell,
hold down the left mouse button as you drag down to the last cell in the
column you want, then Paste either with the Paste button on the toolbar, or
right click the mouse and choose Paste.


Regards

Roger Govier

 

[Guest]

Hi Roger,
Thankyou yet again, for your explanation.
I have now got the "Paste Special Value" to work.....can you advise me
further....

Question 1.
Quote
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).

I find if I enter the formula say in Z1:- =PROPER(A1), the formatting is
happening in cell Z1 not in A1 where I would like it.

If I enter my name in cell Z1 I overwrite the formula.

Form your decription, I though entering the proper formula: =PROPER(B1) that
cell B1 would be formatted when I typed my name into cell B1, but when I do
this the formatting is showing in cell Z1......
When I delete Z1 I effectively delete the formatting, if you know what I mean.
I hope I have explained myself well.
Please advise.

Question 2.
I know understand how to copy and paste some of the cells......is there a
way to format the full column to the same formula. what enters my mind is if
I only format some cells there will come a ime when the last formatted cell
arrives and I may not be avaiable to format more cells appropriatly.
Please advise

Regards
Dermot
Regards
Dermot

 

[Roger Govier]

Hi Dermot

I'm obviously not managing to explain myself very well.
As I understand it, you have names in column B which are not formatted
correctly, as in the names don't start with capital letters.

You cannot do anything to column B by way of formatting or formulae to
affect what is in column B.
So, in column Z, we enter the formula =PROPER(B1).
This will copy the contents of cell B1 to Z1 and in the process it will
convert the first letters of the names to Capitals.

By copying the formula down column Z for as many rows as there is data in
column B, the formula will adjust and will copy each successive cell value
down column B and convert it.

Column B will still be as it was originally.
Now, copying column Z and Paste Special>Values over the top of column B,
will replace the incorrect values in B, with the now corrected values from
column Z.

Once this process has been carried out, then column Z is superfluous to
requirements, and can be removed (if you wish).
I was assuming that any new entries into column B would be made in the
correct way.


Regards

Roger Govier

 

[Guest]

Hi Roger,

There has been nothing wong with your explantions to date.
It is my misunderstanding of what can be achieved by the method you are
describing.
Quote
I was assuming that any new entries into column B would be made in the
correct way.

I now realise that I have been asking the wrong question by asking about the
"Proper" format.
I am looking for a more "Dynamic" solution for want to of a better word.

The Question should read as:-
Is there a way to enter names a column of cells to produce the same results
as the "Proper" format, but to be a permanent feature,in a full column, so
that all names entered are of the correct appearance. Others will use the
sheet and enter all text in lower case....the automation formatting it as I
would like it, and the individual need only enter the data.....any errors in
the appearance ie. caps on first letters....being corrected automatically.

Thanks for your patience

Dermot