Q:

In Excel, how can I reference a cell's appearance instead of its underneath value?

In Excel, how can I reference a cell's appearance (the displayed result) instead of its underneath value? For example:

Type 3.1415 in A1 and press Ctrl + Enter
Press Ctrl + Shift + $, A1 will display the value as $3.14
Press Alt + h 0 (hold Alt, press h, release both, press 0), A1 will display $3.141
In A2, type ="Amount: $3.141", A2 will display Amount: $3.141.

My question is, in the cell A2, is there a way to reference A1 and display Amount: $3.141 instead of hardcoded $3.141?

A:

As you have found the display is just a mask of the real number.  To do what you want you can apply the mask using TEXT and return a string in the same format:
="Amount: " & TEXT(A1,"$#,##0.000")

But the format is static.  The number will change as A1 changes, but the format or mask will not change if you change the format on A1.
BTW, All those keyboard short cuts can be done by simply applying a custom format of $#,##0.000

Now with a little vba we can actually use the text(display) value of the cell.  Put this code in a module attached to the workbook.
Function MyStr(rng As Range)
    MyStr = "Amount: " & rng.Text
End Function

And then you can call it from the sheet like a formula:
=MyStr(A1)

This will return what is displayed, so as the format is changed, you will need to force the recalculation of the sheet(F9), so will the output.

