Return the last updated value with the cell formatting

msantosh1220

New Member
Joined
Jul 3, 2018
Messages
5
Dear Excel friend,

I need your help in finding the last value updated along with the cell formatting.

I have a table with some country listing in A column and currency amount in the next column
The amount columns are formatted on currency as per the country code

[TABLE="width: 534"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ISO[/TD]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]02-Feb-18[/TD]
[TD="align: right"]03-Mar-18[/TD]
[TD="align: right"]01-Apr-18[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD="align: right"]USD 25.00[/TD]
[TD="align: right"]USD 300.00[/TD]
[TD="align: right"]USD 230.00[/TD]
[TD="align: right"]USD 180.00[/TD]
[/TR]
[TR]
[TD]CANADA[/TD]
[TD] [/TD]
[TD="align: right"]USD 160.00[/TD]
[TD] [/TD]
[TD="align: right"]CAD 120.00[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD="align: right"]EUR 120.00[/TD]
[TD="align: right"]GBP 125.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]GBP 200.00[/TD]
[/TR]
[TR]
[TD]FRANCE[/TD]
[TD="align: right"]EUR 120.00[/TD]
[TD="align: right"]EUR 130.00[/TD]
[TD="align: right"]EUR 145.00[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I am looking for a formula which will return the last value and keep the currency formatting.


Note: Canada and UK has few blanks and two different currency code, the formula has to pick the last currency formatting that is CAD and GBP.



Thanking you in Advance

Regards
msantosh1220
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sorry as I did not provide sufficient information of what I was looking form above data


I would like to have a function that give the last updated value along with formatting for each row, as in below case


[TABLE="width: 635"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ISO[/TD]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]02-Feb-18[/TD]
[TD="align: right"]03-Mar-18[/TD]
[TD="align: right"]01-Apr-18[/TD]
[TD]Output_last value[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD="align: right"]USD 25.00[/TD]
[TD="align: right"]USD 150.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]USD 150.00[/TD]
[/TR]
[TR]
[TD]CANADA[/TD]
[TD][/TD]
[TD="align: right"]USD 160.00[/TD]
[TD][/TD]
[TD="align: right"]CAD 120.00[/TD]
[TD="align: right"]CAD 120.00[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD="align: right"]EUR 120.00[/TD]
[TD="align: right"]EUR 125.00[/TD]
[TD][/TD]
[TD="align: right"]GBP 200.00[/TD]
[TD="align: right"]GBP 200.00[/TD]
[/TR]
[TR]
[TD]FRANCE[/TD]
[TD="align: right"]EUR 120.00[/TD]
[TD="align: right"]EUR 130.00[/TD]
[TD="align: right"]EUR 145.00[/TD]
[TD][/TD]
[TD="align: right"]EUR 145.00[/TD]
[/TR]
</tbody>[/TABLE]


"Output_last value" column should return the last updated value along with cell formatting

I have tried using =Lookup(9e+10,B2:E2), this formula gives me last updated column but does not provide the cell formatting.

I was looking for a formula that will fetch me both the value and cell formatting.
Looking forward for your assistance on the above.

Regards
msantosh1220
 
Upvote 0
Formulas cannot read cell formats, well not properly, you can use CELL("format",cell reference) but it will only read the type of data, General, Number, etc and even then it's very limited.
You can't read bold, colour, italics etc
You need VBA to read the full format of a cell, especially colour.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top