Excel 2003 formula

[Ginger]

I'm trying to make a formula for a list to check and strike out another on 3
sheets. Only 2 are using the info, sheet 3 has various colums to check.

Sheet 2
A B
dog yes
cat (blank)

Sheet 3
A C E etc...
Dog(striked) Blank Dog(striked)

If sheet 2, B is yes then check Sheet 3 colums and strike them out or change
color. Hope
that made sense. Similar to a called off list.

Please help me if you can.
TIA
Ginger

 

[tompl]

If you are familiar with conditional formatting you could apply conditional
formatting using a formula similar to this:
“=IF(VLOOKUP(A1,'Sheet 2'!A:B,2,FALSE)="yes",TRUE,FALSE)
where A1 is the cell being formatted. Then choose the strikethrough format
for when the condition is true.

Tom

 

[David Biddulph]

You don't need IF(...,TRUE,FALSE)
The formula =(VLOOKUP(A1,'Sheet 2'!A:B,2,FALSE)="yes" will already return
the Boolean values TRUE or FALSE, so your IF function adds nothing.

 

[Ginger]

Thanks but still not working. Excel takes the conditional formula but the
sheet isn't changing. I tried both formulas with the same results. Any other
suggestions? I could email it to one of you?

 

[David Biddulph]

I was commenting on tompl's formula in isolation, rather than on its
suitability as an answer to your question, as he had snipped your question
when he gave the answer. Looking at the full story, it doesn't look as if
either version would work for you (even correcting the obvious parenthesis
mismatch in my answer), at least on my version of Excel (2007), as CF
doesn't allow criteria that refer to other worksheets in that way. I'm
surprised that you say "Excel takes the conditional formula but the sheet
isn't changing.", as in my case Excel will not accept the formula as a
criterion.

Perhaps tompl may wish to reconsider his answer, or perhaps it only works on
some versions of Excel?

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Don Guillett]

CF does not like other sheet references but you can fool it by simply
defining a name on the words sheet lookupwords= $a1:$b$18 or make it dynamic
by using this formula to define the name
=offset($a$1,0,0,counta($a:$a),2)

then use this in the players sheet for your CF
=VLOOKUP(E2,lookupwords,2,0)="yes"
and format as desired in black or strike