Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formatting Cell Using Formulae....advise please....
I am trynig to understand how to format a column to use the "Proper" argument.
The help example appears to be for one cell only I whould 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? I would also like to know how to format a column so the when I press the letter Y or N it enters Yes or No as typed here? Any other suggestions how I can get to grips with the formatting ffeatures would be appreciated. |
#2
|
|||
|
|||
Formatting Cell Using Formulae....advise please....
On the first point, you would enter the PROPER formula in an adjacent cell,
then copy down for all rows. The second would require VBA. Formatting does not change the content, just its appearance, so it will not make ordinary text, proper case text, nor change Y to Yes. -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... I am trynig to understand how to format a column to use the "Proper" argument. The help example appears to be for one cell only I whould 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? I would also like to know how to format a column so the when I press the letter Y or N it enters Yes or No as typed here? Any other suggestions how I can get to grips with the formatting ffeatures would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Cell Using Formulae....advise please....
Hi Bob,
Quote The second would require VBA. I have looked for some code to do this bob..... could you suggest some, thanks Dermot "Bob Phillips" wrote: On the first point, you would enter the PROPER formula in an adjacent cell, then copy down for all rows. The second would require VBA. Formatting does not change the content, just its appearance, so it will not make ordinary text, proper case text, nor change Y to Yes. -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... I am trynig to understand how to format a column to use the "Proper" argument. The help example appears to be for one cell only I whould 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? I would also like to know how to format a column so the when I press the letter Y or N it enters Yes or No as typed here? Any other suggestions how I can get to grips with the formatting ffeatures would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Cell Using Formulae....advise please....
Hi Bob,
I do understand what the Proper function does now, but it does not do for me what I thought it would to do. Initial Question Reworded What is the most commonly used way to format text in a spreadsheet for best readability and appearance? How would I achieve this? Thanks Dermot "Dermot" wrote: Hi Bob, Quote The second would require VBA. I have looked for some code to do this bob..... could you suggest some, thanks Dermot "Bob Phillips" wrote: On the first point, you would enter the PROPER formula in an adjacent cell, then copy down for all rows. The second would require VBA. Formatting does not change the content, just its appearance, so it will not make ordinary text, proper case text, nor change Y to Yes. -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... I am trynig to understand how to format a column to use the "Proper" argument. The help example appears to be for one cell only I whould 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? I would also like to know how to format a column so the when I press the letter Y or N it enters Yes or No as typed here? Any other suggestions how I can get to grips with the formatting ffeatures would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Cell Using Formulae....advise please....
Hi Dermot,
The best way to fill out a spreadsheet is to fill it out correctly the first time. I can be as ambiguous as you are. Now then since I think you are still looking to use Proper Case (or Title Case) you are talking about formatting and it is not a format in Excel. Also a function (formula if you like) cannot change the formatting of any cell including itself. And you do not want a function referring to it's own value as that would be a circular reference and very few aplications work that very well. Like a dog chasing it's tail. In order to use the PROPER Worksheet Function you would need to use a helper column (meaning another column with formulas). This can get messy as your intent is to change the original column. So you would be much better off making the corrections to your column at one shot with a macro. The Proper_case macro on my page will ignore formulas but will handle Text constants quite well. http://www.mvps.org/dmcritchie/excel/proper.htm You will also see a description of why you would not want to use a formula to make the change. Microsoft Word, I've read, allows you to format as proper, but not Excel. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Dermot" wrote in message ... Hi Bob, I do understand what the Proper function does now, but it does not do for me what I thought it would to do. Initial Question Reworded What is the most commonly used way to format text in a spreadsheet for best readability and appearance? How would I achieve this? Thanks Dermot "Dermot" wrote: Hi Bob, Quote The second would require VBA. I have looked for some code to do this bob..... could you suggest some, thanks Dermot "Bob Phillips" wrote: On the first point, you would enter the PROPER formula in an adjacent cell, then copy down for all rows. The second would require VBA. Formatting does not change the content, just its appearance, so it will not make ordinary text, proper case text, nor change Y to Yes. -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... I am trynig to understand how to format a column to use the "Proper" argument. The help example appears to be for one cell only I whould 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? I would also like to know how to format a column so the when I press the letter Y or N it enters Yes or No as typed here? Any other suggestions how I can get to grips with the formatting ffeatures would be appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Cell Using Formulae....advise please....
Thank you very much for the reply and links David.
I did not intend to be ambiguous, but when I read over my question again it is very vague. Being a beginner to excel, and excel being complex I just want to ensure I learn good methods of presentation as well as content in the speadsheets I make up. I have seen some complex spreadsheets by some people and to me they are awful to look at. Thanks again for your time and explanations Dermot "David McRitchie" wrote: Hi Dermot, The best way to fill out a spreadsheet is to fill it out correctly the first time. I can be as ambiguous as you are. Now then since I think you are still looking to use Proper Case (or Title Case) you are talking about formatting and it is not a format in Excel. Also a function (formula if you like) cannot change the formatting of any cell including itself. And you do not want a function referring to it's own value as that would be a circular reference and very few aplications work that very well. Like a dog chasing it's tail. In order to use the PROPER Worksheet Function you would need to use a helper column (meaning another column with formulas). This can get messy as your intent is to change the original column. So you would be much better off making the corrections to your column at one shot with a macro. The Proper_case macro on my page will ignore formulas but will handle Text constants quite well. http://www.mvps.org/dmcritchie/excel/proper.htm You will also see a description of why you would not want to use a formula to make the change. Microsoft Word, I've read, allows you to format as proper, but not Excel. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Dermot" wrote in message ... Hi Bob, I do understand what the Proper function does now, but it does not do for me what I thought it would to do. Initial Question Reworded What is the most commonly used way to format text in a spreadsheet for best readability and appearance? How would I achieve this? Thanks Dermot "Dermot" wrote: Hi Bob, Quote The second would require VBA. I have looked for some code to do this bob..... could you suggest some, thanks Dermot "Bob Phillips" wrote: On the first point, you would enter the PROPER formula in an adjacent cell, then copy down for all rows. The second would require VBA. Formatting does not change the content, just its appearance, so it will not make ordinary text, proper case text, nor change Y to Yes. -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... I am trynig to understand how to format a column to use the "Proper" argument. The help example appears to be for one cell only I whould 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? I would also like to know how to format a column so the when I press the letter Y or N it enters Yes or No as typed here? Any other suggestions how I can get to grips with the formatting ffeatures would be appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Cell Using Formulae....advise please....
Hi Dermot,
Thanks, Use of newsgroups certainly helps learn English even for those whose only language is English, and it helps to improve making oneself clear. Along the lines of what you were originally trying to you might look at use of the Fillhandle and the mouse at http://www.mvps.org/dmcritchie/excel/fillhand.htm but as indicated before you want to use a macro for this. Also look at the Tutorials at http://www.mvps.org/dmcritchie/excel....htm#tutorials among which I would suggest looking at the Data Pig Technologies Flash presentations. They are just a few minutes each and Mike Alexander did a very good job on them. As far as recognizing decent macros from newsgroups and in writing your own I would suggest looking over http://www.mvps.org/dmcritchie/excel/proper.htm http://www.mvps.org/dmcritchie/excel/slowresp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Dermot" wrote ... Thank you very much for the reply and links David. I did not intend to be ambiguous, but when I read over my question again it is very vague. Being a beginner to excel, and excel being complex I just want to ensure I learn good methods of presentation as well as content in the speadsheets I make up. I have seen some complex spreadsheets by some people and to me they are awful to look at. Thanks again for your time and explanations Dermot |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Cell Using Formulae....advise please....
Hi David,
That was an unexpected bonus, thanks for these links. Much appreciated Dermot "David McRitchie" wrote: Hi Dermot, Thanks, Use of newsgroups certainly helps learn English even for those whose only language is English, and it helps to improve making oneself clear. Along the lines of what you were originally trying to you might look at use of the Fillhandle and the mouse at http://www.mvps.org/dmcritchie/excel/fillhand.htm but as indicated before you want to use a macro for this. Also look at the Tutorials at http://www.mvps.org/dmcritchie/excel....htm#tutorials among which I would suggest looking at the Data Pig Technologies Flash presentations. They are just a few minutes each and Mike Alexander did a very good job on them. As far as recognizing decent macros from newsgroups and in writing your own I would suggest looking over http://www.mvps.org/dmcritchie/excel/proper.htm http://www.mvps.org/dmcritchie/excel/slowresp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Dermot" wrote ... Thank you very much for the reply and links David. I did not intend to be ambiguous, but when I read over my question again it is very vague. Being a beginner to excel, and excel being complex I just want to ensure I learn good methods of presentation as well as content in the speadsheets I make up. I have seen some complex spreadsheets by some people and to me they are awful to look at. Thanks again for your time and explanations Dermot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible Lookup Table | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
Protect Cell Formatting | Excel Worksheet Functions | |||
Formatting dates in the future | Excel Worksheet Functions | |||
Cell Formatting | Excel Discussion (Misc queries) |