Assistance with Formula

[Guest]

Hello,

I have a workbook which I produce my Sales Quotes in.

Basically, on one page, I feed in all the relevant information and then
through various formulas (eg. =B29) where it looks to see what's in the cell
where I've fed in the info, it produces the quote on a standard template,
just with the relevant info filled in.

So far, we can only produce the quotes in one currency and I want to be able
to select different currencies (ie. Euro's, Dollars). What I need to be able
to do is select the currency we want, input the exchange rate and then have
the sheet where the offer is produced recognise that if there is something in
the foreign currency area, it recognises immediately what it is. I've setup
a validation with the list so that we can select the currency and then we
manually input the exchange rate.

Normally, we input a Euro price we automatically converts to Pound Sterling,
if we're just making a Pound Sterling offer. However, to make this in
Euro's, I have to change everything. What it needs to do is, depending on
what currency we select, convert the sterling price by the foreign exchange
rate we feed in and on the quote template in the pricing cells, it needs to
recognise that if there is a foreign currency selected, it needs to show the
symbol (ie € or $) and the price etc.

Sounds complicated but is probably quite simple.

Anyone have any ideas?

Thanks,
Ben,.

 

[Guest]

Ben,
Given you know the currency, you can change format of the currency
cells to show the correct symbol:

Example below shows pounds sterling, US dollars and Euros

Range("G2").Select
Selection.NumberFormat = "$#,##0.00"
Range("G3").Select
Selection.NumberFormat = "[$$-409]#,##0.00"
Range("G4").Select
Selection.NumberFormat = "[$€-2] #,##0.00"

Does this help?

 

[Guest]

That looks about right, Toppers.

Although how would I incorporate this within the template itself?
Remember that it needs to make the necessary conversions between currencies
and it only needs to do this if a foreign currency is being worked with

Ben,
Given you know the currency, you can change format of the currency
cells to show the correct symbol:

Example below shows pounds sterling, US dollars and Euros

Range("G2").Select
Selection.NumberFormat = "$#,##0.00"
Range("G3").Select
Selection.NumberFormat = "[$$-409]#,##0.00"
Range("G4").Select
Selection.NumberFormat = "[$€-2] #,##0.00"

Does this help?

Hello,

I have a workbook which I produce my Sales Quotes in.

Basically, on one page, I feed in all the relevant information and then
through various formulas (eg. =B29) where it looks to see what's in the cell
where I've fed in the info, it produces the quote on a standard template,
just with the relevant info filled in.

So far, we can only produce the quotes in one currency and I want to be able
to select different currencies (ie. Euro's, Dollars). What I need to be able
to do is select the currency we want, input the exchange rate and then have
the sheet where the offer is produced recognise that if there is something in
the foreign currency area, it recognises immediately what it is. I've setup
a validation with the list so that we can select the currency and then we
manually input the exchange rate.

Normally, we input a Euro price we automatically converts to Pound Sterling,
if we're just making a Pound Sterling offer. However, to make this in
Euro's, I have to change everything. What it needs to do is, depending on
what currency we select, convert the sterling price by the foreign exchange
rate we feed in and on the quote template in the pricing cells, it needs to
recognise that if there is a foreign currency selected, it needs to show the
symbol (ie € or $) and the price etc.

Sounds complicated but is probably quite simple.

Anyone have any ideas?

Thanks,
Ben,.

 

[Guest]

You will probably need to write VBA code as I don't believe you change the
formats using formulae.

Are you able to do this?

That looks about right, Toppers.

Although how would I incorporate this within the template itself?
Remember that it needs to make the necessary conversions between currencies
and it only needs to do this if a foreign currency is being worked with

Ben,
Given you know the currency, you can change format of the currency
cells to show the correct symbol:

Example below shows pounds sterling, US dollars and Euros

Range("G2").Select
Selection.NumberFormat = "$#,##0.00"
Range("G3").Select
Selection.NumberFormat = "[$$-409]#,##0.00"
Range("G4").Select
Selection.NumberFormat = "[$€-2] #,##0.00"

Does this help?

Hello,

I have a workbook which I produce my Sales Quotes in.

Basically, on one page, I feed in all the relevant information and then
through various formulas (eg. =B29) where it looks to see what's in the cell
where I've fed in the info, it produces the quote on a standard template,
just with the relevant info filled in.

So far, we can only produce the quotes in one currency and I want to be able
to select different currencies (ie. Euro's, Dollars). What I need to be able
to do is select the currency we want, input the exchange rate and then have
the sheet where the offer is produced recognise that if there is something in
the foreign currency area, it recognises immediately what it is. I've setup
a validation with the list so that we can select the currency and then we
manually input the exchange rate.

Normally, we input a Euro price we automatically converts to Pound Sterling,
if we're just making a Pound Sterling offer. However, to make this in
Euro's, I have to change everything. What it needs to do is, depending on
what currency we select, convert the sterling price by the foreign exchange
rate we feed in and on the quote template in the pricing cells, it needs to
recognise that if there is a foreign currency selected, it needs to show the
symbol (ie € or $) and the price etc.

Sounds complicated but is probably quite simple.

Anyone have any ideas?

Thanks,
Ben,.