Formula to enter into Conditional Formatting Dialog area

[jim may]

I have a column filled with the following formula:

=SUM(INDIRECT"'"&VLOOKUP($C$1,LookUpData!A:F,6,FALSE)&"'!Chase"))

But from time to time, after a review -- I need to add "1" to the $C$1 (numeric entry) to several cells only.

I'm hoping to use C/F to highlight ALL CELLS that contain the "$C$1+1" cell reference, like the below:


=SUM(INDIRECT"'"&VLOOKUP($C$1+1,LookUpData!A:F,6,FALSE)&"'!Chase"))

How can this be achieved?

Tks in Advance.

Jim

 

[Marcelo Branco]

Hi Jim

Maybe you can use the function FORMULATEXT (available on Excel 2013 or higher).

Something like this
=ISNUMBER(SEARCH("$C$1+1",FORMULATEXT(A2)))

I'm using Excel 2010 so i cannot test if this works.

M.

 

[Tetra201]

Maybe just:

Conditional Formatting --> New Rule... --> Format only cells that contain --> Cell value, equal to, =SUM(INDIRECT("'"&VLOOKUP($C$1+1,LookUpData!A:F,6,FALSE)&"'!Chase"))

 

[jim may]

Marcelo,

Thanks -- I'm running Excel 2010 -- Appartently, the FORMULATEXT() function didn't come along until Excel 2013.

I got it another way;;; by using a UDF "ShowFormula"

and in C/F entering:

=SEARCH("$C$1+1",ShowFormula(E58))>0

Works FINE!!!

Again Thanks,

Jim