Return the last updated value with the cell formatting

[msantosh1220]

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

 

[Special-K99]

So what would your expected output be from this data?

 

[msantosh1220]

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

 

[Special-K99]

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.