conditional format if cell contains link to another cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marcel Marien - 28 Oct 2006 20:26 GMT

Hello,

is it possible to format a cell conditionally in such a way, that the
content appears in a certain format if the cell contains a value or text and
in another format if the cell contains the address of another cell?

Thank you very much in advance,
Marcel

Reply to this Message

Héctor Miguel - 29 Oct 2006 08:45 GMT

hi, Marcel !

> is it possible to format a cell conditionally in such a way
> that the content appears in a certain format if the cell contains a value or text
> and in another format if the cell contains the address of another cell?

1) for a contitional formulae to format a cell if it's contents is value/text [I guess]...
you could use either isnumber(...) and/or istext(...) worksheet functions
either way, use them as 2nd [and 3rd] condition/s in format conditions...

2) as the 1st formula in format conditions... [and if you feel comfortable using xl4 macro-functions]
assuming 'the cell' is 'A1'... -> select 'A1' and define a named-formula [insert / name / define...]
a) i.e. name: -> Is.Ref [you can use dots in this way]
b) in the 'refers to:' edit box, enter the following -very- looong formula:
-> it should be a single line, i'm breaking it to make it 'easy-read'...

=len(formula.convert(get.cell(6+0*now(),!a1),1,0,4,!b1))<>
len(substitute(formula.convert(get.cell(6+0*now(),!a1),1,0,4,!b1)
,index(get.workspace(37),6)&index(get.workspace(37),10),""))

c) use 'the name' [is.ref] as the 1st formula condition [in this case for your 'A1' cell]

notes:

1) you could break this looong formula into 2 or 3 'shorter' names [but]...
for each re/calculation changes of your 'condition'... you *will* have 2 or 3 names re/calculating
and IF you will be using 'that' format condition in several/many cells... :-((

2) if your excel's language is not english, xl4 macro-functions [formula.convert, get.cell and get.workspace]
it *shall* be entered in your excel's language

3) the use of get.workspace is to get the characters for row-letter and left-bracket in relative R1C1 style [*just in case*]

if any doubts [or further information]... would you please comment ?
hth,
hector.

Reply to this Message

Héctor Miguel - 29 Oct 2006 09:58 GMT

hi [again], Marcel !

i've found a shorter formula for defining 'the name'... try with:

=isnumber(search(index(get.workspace(37),6)&index(get.workspace(37),10),formula.convert(get.cell(6+0*now(),!a1),1,0,4,!b1)))

regards,
hector.

__ previous posts __

>> is it possible to format a cell conditionally in such a way
>> that the content appears in a certain format if the cell contains a value or text
[quoted text clipped - 30 lines]
> hth,
> hector.

Reply to this Message

Marcel Marien - 02 Nov 2006 01:28 GMT

Hi Héctor,

thank you very much for your answer - you seem to have put quite some
thinking into it. Unfortunately I can't really follow this thinking
(yet??). - And maybe I have not stated my problem clearly, I actually
thought it was rather simple and common. In some cells I directly enter
data, in some cells I just enter the address of a cell that contains the
data. I want to display the cells that contain original data differently
than the cells that contain addresses and I do not want to format them
manually.

Marcel

> hi [again], Marcel !
>
[quoted text clipped - 54 lines]
>> hth,
>> hector.

Reply to this Message

Rate this thread: