Ability to change currency as needed across a spreadsheet in Calc?

users
#1

Hello LibreOffice fans...

I'm looking for some help with calc on a spreadsheet that I have set up. I'm
not sure if it needs a macro set up, or if there's something in LibreOffice
that will do this for me, or if I have to seek custom development to do
this....

Here's the problem:

- I have a large risk management spreadsheet that contains a number of cells
with currency based risk numbers in it; some of these are manually entered
by the user and some are calculated from cells where the user enters the
information.
- Currently, the financial risk cells are all in USD ($) currency.
- If I have a user in another country that wants to use this spreadsheet,
I'd like that person to be able to either press a button, or select from a
drop-down list, a list of available currencies to choose from.
- When they choose their currently, I want a macro (or something!) to go and
find all the US currency based cells in the spreadsheet and change them to
the currency selected by the user.
- If I can get this to work, then this spreadsheet could pretty much be used
in any country/currency and the user would not have to go into every single
currency based cell and make the changes needed (which would be time
prohibitive).
- i.e. Looking for a minimum click solution to achieve this. I do realize
that the local install will change the currency for the user by default, but
I want to be able to over-ride this so that if a local user sees the
spreadsheet in USD and changes it to, for example, GBP, then when he sends
it back to me, I see the numbers in GBP and not my local language setting
(which would default back to USD).

I hope I have explained this clearly! If not please don't hesitate to
ask...

Has anyone done this before or have any ideas how to achieve what I am
looking for?

Many thanks in advance.

Regards,

Geoff

#2

Create a specific cell style for currency, and change the currency when needed.

jonathon

#3

Hi Geoff,

Hello LibreOffice fans...

I'm looking for some help with calc on a spreadsheet that I have set up. I'm
not sure if it needs a macro set up, or if there's something in LibreOffice
that will do this for me, or if I have to seek custom development to do
this....

Here's the problem:

- I have a large risk management spreadsheet that contains a number of cells
with currency based risk numbers in it; some of these are manually entered
by the user and some are calculated from cells where the user enters the
information.
- Currently, the financial risk cells are all in USD ($) currency.
- If I have a user in another country that wants to use this spreadsheet,
I'd like that person to be able to either press a button, or select from a
drop-down list, a list of available currencies to choose from.
- When they choose their currently, I want a macro (or something!) to go and
find all the US currency based cells in the spreadsheet and change them to
the currency selected by the user.
- If I can get this to work, then this spreadsheet could pretty much be used
in any country/currency and the user would not have to go into every single
currency based cell and make the changes needed (which would be time
prohibitive).
- i.e. Looking for a minimum click solution to achieve this. I do realize
that the local install will change the currency for the user by default, but
I want to be able to over-ride this so that if a local user sees the
spreadsheet in USD and changes it to, for example, GBP, then when he sends
it back to me, I see the numbers in GBP and not my local language setting
(which would default back to USD).

I hope I have explained this clearly! If not please don't hesitate to
ask...

Has anyone done this before or have any ideas how to achieve what I am
looking for?

Many thanks in advance.

Regards,

Geoff

--
View this message in context: http://nabble.documentfoundation.org/Ability-to-change-currency-as-needed-across-a-spreadsheet-in-Calc-tp3169381p3169381.html
Sent from the Users mailing list archive at Nabble.com.

If I understand you problem, you want to show the currency in a user
defined manner. One possibility is to have a cover worksheet that
summarizes the data in any currency. You might use a macro to set the
proper currency and exchange rates.

One idea that might make finding all the cells with data to be converted
is to name each one as a one-cell range (ex the cell sheet4!C3 is named
'Fred').

#4

thanks Jonathon... can you provide more details on how you do that and how it
will function when you change the currency?

Just not sure I understand what you mean...

Thank you!

/g

#5

In news:1310666739.4101.16.camel@planas-pinguy,
planas <jslozier@gmail.com> typed:

Hi Geoff,

Hello LibreOffice fans...

I'm looking for some help with calc on a spreadsheet
that I have set up. I'm
not sure if it needs a macro set up, or if there's
something in LibreOffice
that will do this for me, or if I have to seek custom
development to do
this....

Here's the problem:

- I have a large risk management spreadsheet that
contains a number of cells
with currency based risk numbers in it; some of these
are manually entered
by the user and some are calculated from cells where the
user enters the
information.
- Currently, the financial risk cells are all in USD ($)
currency.
- If I have a user in another country that wants to use
this spreadsheet,
I'd like that person to be able to either press a
button, or select from a
drop-down list, a list of available currencies to choose
from. - When they choose their currently, I want a macro
(or something!) to go and
find all the US currency based cells in the spreadsheet
and change them to
the currency selected by the user.
- If I can get this to work, then this spreadsheet could
pretty much be used
in any country/currency and the user would not have to
go into every single
currency based cell and make the changes needed (which
would be time
prohibitive).
- i.e. Looking for a minimum click solution to achieve
this. I do realize
that the local install will change the currency for the
user by default, but
I want to be able to over-ride this so that if a local
user sees the
spreadsheet in USD and changes it to, for example, GBP,
then when he sends
it back to me, I see the numbers in GBP and not my local
language setting (which would default back to USD).

I hope I have explained this clearly! If not please
don't hesitate to
ask...

Has anyone done this before or have any ideas how to
achieve what I am
looking for?

Many thanks in advance.

Regards,

Geoff

--
View this message in context:
http://nabble.documentfoundation.org/Ability-to-change-currency-as-needed-across-a-spreadsheet-in-Calc-tp3169381p3169381.html
Sent from the Users mailing list archive at Nabble.com.

If I understand you problem, you want to show the
currency in a user defined manner. One possibility is to
have a cover worksheet that summarizes the data in any
currency. You might use a macro to set the proper
currency and exchange rates.

One idea that might make finding all the cells with data
to be converted is to name each one as a one-cell range
(ex the cell sheet4!C3 is named 'Fred').

--
Jay Lozier
jslozier@gmail.com

You could only make that work if you included a look-up on the exchange
rates for every monetary system you wanted to use as they change daily, even
hourly in some cases, and a number today m,ight not be accurate tomorrow.
How much accuracy do you requre in other words? You could take a bath in
some cases.

Otherwise, just set up your currency multipliers in hidden columns and do
the multiplication.

HTH,

Twayne`

#6

Hi

  Excuse me but I think you have another problem (Not only change de
currency symbol):

  The type change currency between USD and other countries (If you want
that the people looks a correct value).

Regards,

Jorge

#7

thanks Twayne - I'm actually not interested in 'coverting' from USD to <pick
your currency> so exchange rates don't matter at all... all I really need
to do is be able to say "hey, here's a spreadsheet that's in USD... if you
click on this button you can change the currency to <pick your currency> and
then input the financial/risk information"...

So basically the numbers are going to be static based on the formulae and
what the person inputs; I just want them to be able to do this in their
local currency.

Hope this helps...

/g

#8

thanks Jay... I'll look into this and see if this is do-able/works...

/g

#9

floridabrits wrote:

thanks Twayne - I'm actually not interested in 'coverting' from USD to <pick
your currency> so exchange rates don't matter at all...  all I really need
to do is be able to say "hey, here's a spreadsheet that's in USD... if you
click on this button you can change the currency to <pick your currency> and
then input the financial/risk information"...

Not having to convert the data makes it a lot easier.

So basically the numbers are going to be static based on the formulae and
what the person inputs; I just want them to be able to do this in their
local currency.

When you say you want them to be able to do this in their local
currency, what exactly does that mean? Do you just want the currency
symbol to display in the cell? Or do you want the locale settings to
change for the sheet (including what is used as decimal symbol)?

Regards
Stephan

#10

Hi,
in my spreadsheets there I usually have a special sheet named Parameters
where I set some varying values such as
- company name
- period (weeknumber, year, month etc.)
- local currency name or sign
- other currencies used
- and other ...

Besides that I have a "Work" sheet which remains hidden from printing
and eventually locked from users which sheet contains only supporting
formulas and computations based on the parameters and data enterd somwhere else.
It usually contains current exchange rates vlookup-ed in a separate spreadsheet.

Currency used in the report is usually placed in the report header
( first several rows of the sheet) e.g. "All numbers in EUR" (that is as a formula for example ="All numbers in "&$Parameters.$D$4 )
or in the column headers of the report actually linked from "Parameters" or "Work".
Changing the currency is then only a matter of adjusting the "Parameters" sheet.

I never use the number format with currency sign, as the reports are better readible, it saves the column width (and the printer toner) and usualy there are not mixed financial values with kilometers, liters or emploies number :slight_smile:

Regards,
Jiri

Dne 14.7.2011 20:58, Twayne napsal(a):

#11

Let, say that in "parameters" sheet you have
- in A1 some label saying "Choose your currency"
- in A2 some initial valu e.g. USD

click to A2 and choose from Data -> Validity ->bookmark Criteria -> field Allow select "Cell range" and into field source enter link
to the range where you have allowed curencies e.g. D1:D4
( in D1:D4 should then be e.g. USD, EUR, GBP, PLZ )

The user will be able to expand the list and to select what he/she needs when the cell A2 gets the focus (when the cursor is in the cell).

You can fill in the bookmarks "Input Help" and "Error alert", too.
The users will be happy :slight_smile:

Jiri

Dne 14.7.2011 17:04, floridabrits napsal(a):
...

#12

Hi

  If you don't have a lot of currency to chose by the users, ... you can
make a macro for each currency format and add all of them into new bar.

  I would say you if you need How to do it? It's similar as I explain
another named "footnotes", but quite diferent.

Regards,

Jorge Rodríguez

#13

Stephan - looking for the currency symbol to change and display in whatever
format that current displays in.

Jiri - I'll take a look at your ideas too today and see if I can make that
work.

Thanks for everyone's input so far. Love this forum! :slight_smile:

Regards,

/g