Referncing a Cell's Formatted Result

[PDNY]

Hi,

I'm trying to reference a cell's custom numerical formatted result instead of its cell value. For example:

Cell A1 has a value of =.75 and uses custom format "Pass rate: ("0.0%")". The result Cell A1 displayed is therefore:

Pass rate: (75.0%)

I want Cell A2 to refer to Cell A1's displayed result and not its value (i.e. refer to "Pass rate: (75.0%)" and not ".75").

Anyone know how to do this (especially without VBA but willing to use it)?

Thanks.

 

[AlphaFrog]

This will reference A1 as text using the custom format

="Pass rate: " & TEXT(A1,"(0.0%)")

 

[PDNY]

Many thanks for the reply.

This part of the suggestion:

TEXT(A1,"(0.0%)")

references the 0.75 value of Cell A1 rather than the displayed custom formatted result of "Pass rate: (75.0%)"

The reason for this displayed results requirement is that Cell A1 is referenced as a number (i.e. 0.75) by one set of cells and needs to be referenced by another as a text of its displayed result (FWIW, using the MATCH function). I'm looking for a solution for the latter.

I'd appreciate any thoughts on how to reference the cell's custom formatted displayed result. Thanks.

 

[PDNY]

I should note that using MATCH selects the first cell that matches the criterion, i.e. 0.75, and there are many in the reference cells. The only difference for these referenced cell is their displayed results.

 

[Norie]

You can use what AlphaFrog posted in the MATCH.

=MATCH(TEXT(A1, "Pass Rate: 0.0%"), C1:C100, 0)

Not sure about your additional information/question, do you mean you have values repeated multiple times in the lookup range?

 

[AlphaFrog]

There is no way that I know of to match to a custom format with a formula. It could be done in VBA.

 

[PDNY]

Yes, there are numerous referenced cells in the MATCH lookup array with the same cell value and the function seems to select the first result as the answer.

The only difference between these cells with the same value referenced by MATCH is how they are displayed with custom formatting.

 

[PDNY]

Thanks for the reply. I'm beginning to suspect the same. Please, any VBA suggestions?

 

[Norie]

MATCH always selects the first result.

What is it you want to do?

 

[PDNY]

Match the cell's displayed result (a cell that has been custom formated for display) rather than the cell's value. It's the only way to distinguish one cell result from another.