Extract currency code from another cell to include in a cell's number format ?

[pennep]

Hi,

I searched a lot for this but I couldn't find a solution for this. I wonder if you could help.. To explain my problem, I have created this example. Please see the table below.

I am given Travel cost, Logistic cost and Accommodation cost in GBP (£1200, £1000, £1000 respectively)

These costs needs to be converted into a other currencies using the Exchange Rate as shown in the table. While doing so, Currency Codes from the first cell in each row needs to be included in other cells' (Travel cost, Logistic cost, Accommodation cost and Total). The cost cells are in number format so that figures in these cells could be used for other purposes. (concatenation would make the cell value a TEXT)

I could make it work using conditional formatting, but there are more than 80 currencies and it requires lots of rules.. Is there any smarter way of doing this?

[TABLE="class: outer_border, width: 700"]
[TR]
[TD]Price in GBP[/TD]
[TD][/TD]
[TD]1200[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]3200[/TD]
[/TR]
[TR]
[TD]Currency (LCU)[/TD]
[TD]Exchange Rate[/TD]
[TD]Travel Cost[/TD]
[TD]Logistic costs[/TD]
[TD]Accommodation costs[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]AED[/TD]
[TD]4.8356[/TD]
[TD]AED 5802.72[/TD]
[TD]AED 4835.6[/TD]
[TD]AED 4835.6[/TD]
[TD]AED 15473.92[/TD]
[/TR]
[TR]
[TD]ALL[/TD]
[TD]163.3421[/TD]
[TD]ALL 196010.52[/TD]
[TD]ALL 163342.1[/TD]
[TD]ALL 163342.1[/TD]
[TD]ALL 522694.72[/TD]
[/TR]
[TR]
[TD]AOA[/TD]
[TD]218.2238[/TD]
[TD]AOA 261868.56[/TD]
[TD]AOA 218223.8[/TD]
[TD]AOA 218223.8[/TD]
[TD]AOA 698316.16[/TD]
[/TR]
[TR]
[TD]ARS[/TD]
[TD]19.8267[/TD]
[TD]ARS 23792.04[/TD]
[TD]ARS 19826.7[/TD]
[TD]ARS 19826.7[/TD]
[TD]ARS 63445.44[/TD]
[/TR]
[TR]
[TD]AUD[/TD]
[TD]1.7514[/TD]
[TD]AUD 2101.68[/TD]
[TD]AUD 1751.4[/TD]
[TD]AUD 1751.4[/TD]
[TD]AUD 5604.48[/TD]
[/TR]
[TR]
[TD]BAM[/TD]
[TD]2.3173[/TD]
[TD]BAM 2780.76[/TD]
[TD]BAM 2317.3[/TD]
[TD]BAM 2317.3[/TD]
[TD]BAM 7415.36[/TD]
[/TR]
[TR]
[TD]BGN[/TD]
[TD]2.3173[/TD]
[TD]BGN 2780.76[/TD]
[TD]BGN 2317.3[/TD]
[TD]BGN 2317.3[/TD]
[TD]BGN 7415.36[/TD]
[/TR]
[TR]
[TD]BHD[/TD]
[TD]0.4951[/TD]
[TD]BHD 594.12[/TD]
[TD]BHD 495.1[/TD]
[TD]BHD 495.1[/TD]
[TD]BHD 1584.32[/TD]
[/TR]
[TR]
[TD]BOB[/TD]
[TD]9.1245[/TD]
[TD]BOB 10949.4[/TD]
[TD]BOB 9124.5[/TD]
[TD]BOB 9124.5[/TD]
[TD]BOB 29198.4[/TD]
[/TR]
[TR]
[TD]BRL[/TD]
[TD]4.322[/TD]
[TD]BRL 5186.4[/TD]
[TD]BRL 4322[/TD]
[TD]BRL 4322[/TD]
[TD]BRL 13830.4[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]1.735[/TD]
[TD]CAD 2082[/TD]
[TD]CAD 1735[/TD]
[TD]CAD 1735[/TD]
[TD]CAD 5552[/TD]
[/TR]
[TR]
[TD]CDF[/TD]
[TD]1293.545[/TD]
[TD]CDF 1552254.48[/TD]
[TD]CDF 1293545.4[/TD]
[TD]CDF 1293545.4[/TD]
[TD]CDF 4139345.28[/TD]
[/TR]
[TR]
[TD]CHF[/TD]
[TD]1.2823[/TD]
[TD]CHF 1538.76[/TD]
[TD]CHF 1282.3[/TD]
[TD]CHF 1282.3[/TD]
[TD]CHF 4103.36[/TD]
[/TR]
[TR]
[TD]CLP[/TD]
[TD]868.2831[/TD]
[TD]CLP 1041939.72[/TD]
[TD]CLP 868283.1[/TD]
[TD]CLP 868283.1[/TD]
[TD]CLP 2778505.92[/TD]
[/TR]
[TR]
[TD]CNY[/TD]
[TD]8.7569[/TD]
[TD]CNY 10508.28[/TD]
[TD]CNY 8756.9[/TD]
[TD]CNY 8756.9[/TD]
[TD]CNY 28022.08[/TD]
[/TR]
[TR]
[TD]COP[/TD]
[TD]4078.906[/TD]
[TD]COP 4894686.96[/TD]
[TD]COP 4078905.8[/TD]
[TD]COP 4078905.8[/TD]
[TD]COP 13052498.56[/TD]
[/TR]
[TR]
[TD]CRC[/TD]
[TD]721.2774[/TD]
[TD]CRC 865532.88[/TD]
[TD]CRC 721277.4[/TD]
[TD]CRC 721277.4[/TD]
[TD]CRC 2308087.68[/TD]
[/TR]
[TR]
[TD]CZK[/TD]
[TD]32.0273[/TD]
[TD]CZK 38432.76[/TD]
[TD]CZK 32027.3[/TD]
[TD]CZK 32027.3[/TD]
[TD]CZK 102487.36[/TD]
[/TR]
[TR]
[TD]DKK[/TD]
[TD]8.8121[/TD]
[TD]DKK 10574.52[/TD]
[TD]DKK 8812.1[/TD]
[TD]DKK 8812.1[/TD]
[TD]DKK 28198.72[/TD]
[/TR]
[TR]
[TD]DOP[/TD]
[TD]60.5124[/TD]
[TD]DOP 72614.88[/TD]
[TD]DOP 60512.4[/TD]
[TD]DOP 60512.4[/TD]
[TD]DOP 193639.68[/TD]
[/TR]
[TR]
[TD]DZD[/TD]
[TD]144.8937[/TD]
[TD]DZD 173872.44[/TD]
[TD]DZD 144893.7[/TD]
[TD]DZD 144893.7[/TD]
[TD]DZD 463659.84[/TD]
[/TR]
[TR]
[TD]EGP[/TD]
[TD]11.6888[/TD]
[TD]EGP 14026.56[/TD]
[TD]EGP 11688.8[/TD]
[TD]EGP 11688.8[/TD]
[TD]EGP 37404.16[/TD]
[/TR]
[TR]
[TD]EUR[/TD]
[TD]1.1848[/TD]
[TD]EUR 1421.76[/TD]
[TD]EUR 1184.8[/TD]
[TD]EUR 1184.8[/TD]
[TD]EUR 3791.36[/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD]1[/TD]
[TD]GBP 1200[/TD]
[TD]GBP 1000[/TD]
[TD]GBP 1000[/TD]
[TD]GBP 3200[/TD]
[/TR]
[/TABLE]

Thank you very much for your effort in advance.

Kind regards,
PB

 

[Jeffrey Mahoney]

Are you talking about number formatting? You can format the cells as currency using different monetary symbols.

Jeff

 

[pennep]

Yeah, I am talking about number formatting, but I would like different currency code in each row. The currency code for a row is learnt from the first cell of each row.

I hope I am clear.

 

[Jeffrey Mahoney]

Having the cells in the same row change with any numeric formatting is not possible. A macro could be created that do that. It would be run with a button or something each time you made a change to the table.

 

[pennep]

Thank you Jeffrey for conforming that. I brought this issue here just in case there was a clever method (except using VBA) of doing it.