Formatting numbers

[Guest]

Let's suppose I have 2 sheets in a worksheet.
Sheet 1 is a entry form (dropbox for example)
The source of the dropbox are fields of numbers in different formats
(e.g: %; 0,000, etc)
Sheet 2
This is where the numbers are going to after the user selection. So I
would like to have, for example the following results:
result a)
BRANCH TOTAL
A 1,000
B 45,000

result b)
BRANCH PERCENTAGE
A 44%
B 51%

Note that in result a) I need the cells to be formated as 0,000 and result
b) I want the cells formated as %.

So the question is:
How can I have a cell change its number format based on a condition?

thanks

 

[Guest]

Sorry, let me add something....
I also would like to have the "y" axis of the graph, changing format
according to the results as well (e.g. %, 0,000, etc).
thanks

"avjunior" escreveu:

 

[Guest]

Hello-

Can't say I'm crystal clear on what you are looking to do, but if it will
help there is a feature called Conditional Formatting in the Format Menu. It
does what you seem to be asking for in part of your post, but how the number
gets converted from an integer to a percent may be another issue.

HTH |:>)

 

[Ragdyer]

Can't help you with charting, but if your version of XL is later then XL97,
you could try this custom format:

[>=1]#,###;#%

You must also go to:
<Tools> <Options> <Edit> tab,
And make sure that "Enable Auto Percent Entry"
is *UNCHECKED*

This will display numbers *less* then 1 as percents, and all others as
regular numbers without decimals.
If you think that there might be a possibility that you might have values of
100%, then change the format to this:

[>1]#,###;#%

 

[Guest]

sorry for the poor explation.... English is my second language!

format condition function formats only font, border and patterns. I would
like to format numbers. For example:
if A1="Amount of money" then column A will be formated as 0,000
if A1="Percentage of total" then column A will be formated as 0.00%
thanks for your help

format condition function formats

 

[Guest]

thanks Ragdyer.
It doesn't solve my problem because I have numbers with decimal less than 1
which should be formated as decimal not as percentual. What I want to do is
make a type of format condition for number in order to have:
if A1="Amount of money" then column A will be formated as 0,000
if A1="Percentage of total" then column A will be formated as 0.00%
thanks for your help

Can't help you with charting, but if your version of XL is later then XL97,
you could try this custom format:

[>=1]#,###;#%

You must also go to:
<Tools> <Options> <Edit> tab,
And make sure that "Enable Auto Percent Entry"
is *UNCHECKED*

This will display numbers *less* then 1 as percents, and all others as
regular numbers without decimals.
If you think that there might be a possibility that you might have values of
100%, then change the format to this:

[>1]#,###;#%


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Sorry, let me add something....
I also would like to have the "y" axis of the graph, changing format
according to the results as well (e.g. %, 0,000, etc).
thanks

"avjunior" escreveu:

 

[RagDyer]

How will you designate the contents of the cell to be percent or money?
What will you use to tell XL you're using one or the other?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

thanks Ragdyer.
It doesn't solve my problem because I have numbers with decimal less than 1
which should be formated as decimal not as percentual. What I want to do is
make a type of format condition for number in order to have:
if A1="Amount of money" then column A will be formated as 0,000
if A1="Percentage of total" then column A will be formated as 0.00%
thanks for your help

Can't help you with charting, but if your version of XL is later then XL97,
you could try this custom format:

[>=1]#,###;#%

You must also go to:
<Tools> <Options> <Edit> tab,
And make sure that "Enable Auto Percent Entry"
is *UNCHECKED*

This will display numbers *less* then 1 as percents, and all others as
regular numbers without decimals.
If you think that there might be a possibility that you might have values of
100%, then change the format to this:

[>1]#,###;#%


--
HTH,

RD

--------------------------------------------------------------------------

-

Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-

 

[Guest]

If I understood your questions...

The user select the data filed he/she wants to see through a dropbox. I use
a condition formula, linked to the dropbox linked cell, to identify if the
contents related to the filed is money or percentual.
Please, see the example I sent you by e-mail.
thanks for your time.

How will you designate the contents of the cell to be percent or money?
What will you use to tell XL you're using one or the other?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

thanks Ragdyer.
It doesn't solve my problem because I have numbers with decimal less than 1
which should be formated as decimal not as percentual. What I want to do is
make a type of format condition for number in order to have:
if A1="Amount of money" then column A will be formated as 0,000
if A1="Percentage of total" then column A will be formated as 0.00%
thanks for your help

Can't help you with charting, but if your version of XL is later then XL97,
you could try this custom format:

[>=1]#,###;#%

You must also go to:
<Tools> <Options> <Edit> tab,
And make sure that "Enable Auto Percent Entry"
is *UNCHECKED*

This will display numbers *less* then 1 as percents, and all others as
regular numbers without decimals.
If you think that there might be a possibility that you might have values of
100%, then change the format to this:

[>1]#,###;#%


--
HTH,

RD

--------------------------------------------------------------------------

-

Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-

Sorry, let me add something....
I also would like to have the "y" axis of the graph, changing format
according to the results as well (e.g. %, 0,000, etc).
thanks

"avjunior" escreveu:

Let's suppose I have 2 sheets in a worksheet.
Sheet 1 is a entry form (dropbox for example)
The source of the dropbox are fields of numbers in different
formats
(e.g: %; 0,000, etc)
Sheet 2
This is where the numbers are going to after the user selection. So
I
would like to have, for example the following results:
result a)
BRANCH TOTAL
A 1,000
B 45,000

result b)
BRANCH PERCENTAGE
A 44%
B 51%

Note that in result a) I need the cells to be formated as 0,000 and
result
b) I want the cells formated as %.

So the question is:
How can I have a cell change its number format based on a condition?

thanks