conditional format if cell contains link to another cell

[Marcel Marien]

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

 

[Héctor Miguel]

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.

 

[Héctor Miguel]

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
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.

 

[Marcel Marien]

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 !

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
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.