Hello,

When working with budgeting in Excel 2003, my colleauges normally write
values without prefix on the currency, for example 1.500.000 SEK (Swedish En
Krona). When consolidating the budget, I prefer to display the values in kSEK
(kilo Swedish En Krona). I could do this by making a new spreadsheet and
dividing with 1000, but it does not feel like the right solution. If you can
use percentage formatting of cells, you should be able to do the same with
SEK and kSEK or USD and MUSD? But how?

Thanks in advance,
Peter

How about using a custom format of #,

"Peters" wrote:

Hello,

When working with budgeting in Excel 2003, my colleauges normally write
values without prefix on the currency, for example 1.500.000 SEK (Swedish En
Krona). When consolidating the budget, I prefer to display the values in kSEK
(kilo Swedish En Krona). I could do this by making a new spreadsheet and
dividing with 1000, but it does not feel like the right solution. If you can
use percentage formatting of cells, you should be able to do the same with
SEK and kSEK or USD and MUSD? But how?

Thanks in advance,
Peter

Peter
The problem is that the percent function is a built-in thing that says, when
you select it, that Excel is multiplying by 100 and adding a % symbol (Which
Excel will ignore for calculation purposes). I don't know how to do that.

I'm BRAND new to scripting, but Gord gave me one that worked for my problem
a couple messages up, and I edited it slightly (and tested it) and it should
work for yours.... problems a

1) No 'Undo'... once you've changed them, the only way to change them back
is with another script (after you see this one, it'll be pretty obvious)

2) I don't know how to save a script beyond the workbook it's in. My plan
is to save mine in a Text file and I can just create new macros as needed by
copy/paste.

3) You have to know how to find and use the scripting stuff.

For what it'sworth, here's what I did and what it does:

Sub combine()
Dim i As Range
For Each i In Selection
i.Value = (i.Value/1000)
Next i
End Sub

If you highlight a bunch of column, then play this script, everything in the
column will be divided by 1000.
1500000 becomes 1500
You can add a line to make the next cell over say "kSEK"

i.Value=(i.Value/1000)
i.Offset(0,1).Value=" kSEK"

1500000 becomes 15000 kSEK
and to undo it use a script with these two lines instead:

i.Value=(i.Value*1000)
i.Offset(0,1).Value=""


Better than nothing...

Shane

"Peters" wrote in message
...
Hello,

When working with budgeting in Excel 2003, my colleauges normally write
values without prefix on the currency, for example 1.500.000 SEK (Swedish
En
Krona). When consolidating the budget, I prefer to display the values in
kSEK
(kilo Swedish En Krona). I could do this by making a new spreadsheet and
dividing with 1000, but it does not feel like the right solution. If you
can
use percentage formatting of cells, you should be able to do the same with
SEK and kSEK or USD and MUSD? But how?

Thanks in advance,
Peter

Hi Alok,

This seems to do it, thanks. However, I must say that I do not understand
exactly what I did. The information in the help on the syntax of the custom
formats is very limited. Anyway, it did solve my problem! :-)

/Peter

"Alok" wrote:

How about using a custom format of #,

"Peters" wrote:

Hello,

When working with budgeting in Excel 2003, my colleauges normally write
values without prefix on the currency, for example 1.500.000 SEK (Swedish En
Krona). When consolidating the budget, I prefer to display the values in kSEK
(kilo Swedish En Krona). I could do this by making a new spreadsheet and
dividing with 1000, but it does not feel like the right solution. If you can
use percentage formatting of cells, you should be able to do the same with
SEK and kSEK or USD and MUSD? But how?

Thanks in advance,
Peter

Thanks for help. I am even newer to scripts than you are and I will try this
later on when I start trying scripts. For now, the custom format solution
from Aloks answer worked just fine.

/Peter

"SV" wrote:

Peter
The problem is that the percent function is a built-in thing that says, when
you select it, that Excel is multiplying by 100 and adding a % symbol (Which
Excel will ignore for calculation purposes). I don't know how to do that.

I'm BRAND new to scripting, but Gord gave me one that worked for my problem
a couple messages up, and I edited it slightly (and tested it) and it should
work for yours.... problems a

1) No 'Undo'... once you've changed them, the only way to change them back
is with another script (after you see this one, it'll be pretty obvious)

2) I don't know how to save a script beyond the workbook it's in. My plan
is to save mine in a Text file and I can just create new macros as needed by
copy/paste.

3) You have to know how to find and use the scripting stuff.

For what it'sworth, here's what I did and what it does:

Sub combine()
Dim i As Range
For Each i In Selection
i.Value = (i.Value/1000)
Next i
End Sub

If you highlight a bunch of column, then play this script, everything in the
column will be divided by 1000.
1500000 becomes 1500
You can add a line to make the next cell over say "kSEK"

i.Value=(i.Value/1000)
i.Offset(0,1).Value=" kSEK"

1500000 becomes 15000 kSEK
and to undo it use a script with these two lines instead:

i.Value=(i.Value*1000)
i.Offset(0,1).Value=""


Better than nothing...

Shane

"Peters" wrote in message
...
Hello,

When working with budgeting in Excel 2003, my colleauges normally write
values without prefix on the currency, for example 1.500.000 SEK (Swedish
En
Krona). When consolidating the budget, I prefer to display the values in
kSEK
(kilo Swedish En Krona). I could do this by making a new spreadsheet and
dividing with 1000, but it does not feel like the right solution. If you
can
use percentage formatting of cells, you should be able to do the same with
SEK and kSEK or USD and MUSD? But how?

Thanks in advance,
Peter