Referncing a Cell's Formatted Result

PDNY

New Member
Joined
Jul 26, 2012
Messages
15
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,188
This will reference A1 as text using the custom format

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

PDNY

New Member
Joined
Jul 26, 2012
Messages
15
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

New Member
Joined
Jul 26, 2012
Messages
15
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,188
There is no way that I know of to match to a custom format with a formula. It could be done in VBA.
 

PDNY

New Member
Joined
Jul 26, 2012
Messages
15
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

New Member
Joined
Jul 26, 2012
Messages
15
Thanks for the reply. I'm beginning to suspect the same. Please, any VBA suggestions?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
MATCH always selects the first result.

What is it you want to do?
 

PDNY

New Member
Joined
Jul 26, 2012
Messages
15
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.
 

Forum statistics

Threads
1,085,429
Messages
5,383,620
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top