Hello
Let's say I wish to use a formula something like the following:
=IF(A1="DK", 10.000.000, 10,000,000)

That is, I want to have a number formatted according to Danish respectively US standard depending upon the contents of A1. Danish
standard is period as thousand separator. I wonder if it might be possible to do this by a user defined function. I am aware that I
can change the number handling in the Tools, Options, International dialog box, but then it has effect for the whole workbook. I
need to use Danish number format in one sheet of a workbook and US number format in another sheet.

Hans Knudsen

Hans, You could frig it with a custom format for the US sheet of say

[=1000000]###\,###\,##0.00;[=1000]###\,##0.00;###0.00

I can't test it with my setup, but should work.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hans Knudsen" wrote in message
...
Hello
Let's say I wish to use a formula something like the following:
=IF(A1="DK", 10.000.000, 10,000,000)

That is, I want to have a number formatted according to Danish
respectively US standard depending upon the contents of A1. Danish
standard is period as thousand separator. I wonder if it might be possible
to do this by a user defined function. I am aware that I
can change the number handling in the Tools, Options, International dialog
box, but then it has effect for the whole workbook. I
need to use Danish number format in one sheet of a workbook and US number
format in another sheet.

Hans Knudsen

Bob Phillips
Thanks for your answer.
Unfortunately however - it doesn't work.
As employee in a company working in many different countries I would really like if it was possible to switch between different
country settings as regards number formatting in the formatting dialog box, but as far as I can see this is not possible.

Regards
Hans Knudsen




"Bob Phillips" skrev i en meddelelse ...
Hans, You could frig it with a custom format for the US sheet of say

[=1000000]###\,###\,##0.00;[=1000]###\,##0.00;###0.00

I can't test it with my setup, but should work.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hans Knudsen" wrote in message
...
Hello
Let's say I wish to use a formula something like the following:
=IF(A1="DK", 10.000.000, 10,000,000)

That is, I want to have a number formatted according to Danish
respectively US standard depending upon the contents of A1. Danish
standard is period as thousand separator. I wonder if it might be possible
to do this by a user defined function. I am aware that I
can change the number handling in the Tools, Options, International dialog
box, but then it has effect for the whole workbook. I
need to use Danish number format in one sheet of a workbook and US number
format in another sheet.

Hans Knudsen

In what way does it not work Hans?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hans Knudsen" wrote in message
...
Bob Phillips
Thanks for your answer.
Unfortunately however - it doesn't work.
As employee in a company working in many different countries I would
really like if it was possible to switch between different
country settings as regards number formatting in the formatting dialog
box, but as far as I can see this is not possible.

Regards
Hans Knudsen




"Bob Phillips" skrev i en meddelelse
...
Hans, You could frig it with a custom format for the US sheet of say

[=1000000]###\,###\,##0.00;[=1000]###\,##0.00;###0.00

I can't test it with my setup, but should work.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hans Knudsen" wrote in message
...
Hello
Let's say I wish to use a formula something like the following:
=IF(A1="DK", 10.000.000, 10,000,000)

That is, I want to have a number formatted according to Danish
respectively US standard depending upon the contents of A1. Danish
standard is period as thousand separator. I wonder if it might be
possible
to do this by a user defined function. I am aware that I
can change the number handling in the Tools, Options, International
dialog
box, but then it has effect for the whole workbook. I
need to use Danish number format in one sheet of a workbook and US
number
format in another sheet.

Hans Knudsen

Bob
Excuse my long post.

If I take a copy of:
[=1000000]###\,###\,##0.00;[=1000]###\,##0.00;###0.00
select cell A1, press Ctrl+1 to open the Formatting dialog box, select Custom under Category and press Ctrl+V in the Type field and
then enter respectively 1000000, 1000 and 100 in cell A1 I get the following results:

,1.0,00.000
,1.000
and
100

Maybe I should explain what I amtrying to do in some more detail. I am trying to make a company chart with among other things
company name and amount of share capital for each company. In Sheet1 I use Danish language and Danish standard as regards number
formatting, and in Denmark we use . (point) as thousand separator. That is, a company with a share capital of let's say ten million
DKK I want to show as: DKK 10.000.000

Now I sometimes have to give this company chart to authorities in the US so in Sheet2 I want to set it up according to US standard -
I want the amount to appear as: DKK 10,000,000

If I press Tools, Options, International I have a check mark in the box *Use system separators* under Number handling. If I format a
cell to have thousand separator I will automatically get the . (point) and so it will be for the whole workbook. If I, in Tools,
Options, International remove the check mark in *Use system separators* and set thousand separator to comma then it works fine, but
again, now the comma will be used as thousand separator throughout the workbook.

What I would appreciate is that I could use point as thousand separator in Sheet1 and comma as thousand separator in Sheet2. If I
was the only one working on this file then it might not be a problem, but when you have to share it with other colleagues it is not
any fun having to tell them that they each time must remember to open this Tools, Options dialog and check the Number handling
settings depending on whom they are preparing the chart for.

Regards
Hans



"Bob Phillips" skrev i en meddelelse ...
In what way does it not work Hans?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hans Knudsen" wrote in message
...
Bob Phillips
Thanks for your answer.
Unfortunately however - it doesn't work.
As employee in a company working in many different countries I would
really like if it was possible to switch between different
country settings as regards number formatting in the formatting dialog
box, but as far as I can see this is not possible.

Regards
Hans Knudsen




"Bob Phillips" skrev i en meddelelse
...
Hans, You could frig it with a custom format for the US sheet of say

[=1000000]###\,###\,##0.00;[=1000]###\,##0.00;###0.00

I can't test it with my setup, but should work.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hans Knudsen" wrote in message
...
Hello
Let's say I wish to use a formula something like the following:
=IF(A1="DK", 10.000.000, 10,000,000)

That is, I want to have a number formatted according to Danish
respectively US standard depending upon the contents of A1. Danish
standard is period as thousand separator. I wonder if it might be
possible
to do this by a user defined function. I am aware that I
can change the number handling in the Tools, Options, International
dialog
box, but then it has effect for the whole workbook. I
need to use Danish number format in one sheet of a workbook and US
number
format in another sheet.

Hans Knudsen