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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
This will reference A1 as text using the custom format

="Pass rate: " & TEXT(A1,"(0.0%)")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
There is no way that I know of to match to a custom format with a formula. It could be done in VBA.
 
Upvote 0
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.
 
Upvote 0
Thanks for the reply. I'm beginning to suspect the same. Please, any VBA suggestions?
 
Upvote 0
MATCH always selects the first result.

What is it you want to do?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top