apply formatting to number in ScreenTip (Hyperlink)

Hi all,

I recently built an intuitive map in Excel that illustrates the data that has been selected in the form control combo box on a map. Each prefecture has a 'fake' hyperlink to show the corresponding numbers, hence the ScreenTip needs to adjust every time the metric in the control changes. Everything works well, besides the number format. The metric's name changes, the value changes, even the format changes, but for the metric "Population" and "Sales Retail" the format that is applied is different from the range it gets it from! It is supposed to show $3.48M for "Sales Retail" but instead it shows $3,477,000.00M. For "Population", it's the same issue, it would display 2,450,000.00 Million instead of 2.45 Million. Is this a bug or am I doing something wrong?

Please see sample file below...