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?

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

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.