help with conditional format formula

[dave]

I want a formula that says:

If C1:N1 does not contain a "-" then make C1:N1 = Q1

Basically, I have 100 rows of info that includes job names and
hyphens. Whereever a job name occurs it needs to be replaced with a
number found in row Q.

Takers?

 

[JE McGimpsey]

I want a formula that says:

If C1:N1 does not contain a "-" then make C1:N1 = Q1

Basically, I have 100 rows of info that includes job names and
hyphens. Whereever a job name occurs it needs to be replaced with a
number found in row Q.

Well, you can't change the cell value with a conditional *format*
formula (as per your subject line).

You also can't have both a formula and a manually input value in a
single cell, so you cant set C1:N1 = Q1 based on the content of C1:N1,
without using a macro.

But if you want to return the value in Q1 to *another* cell (say R1) if
C1:N1 doesn't contain a hyphen then you can use a conditional formula in
a cell.

R1: =IF(COUNTIF(C1:N1,"*-*")=0,Q1,"")

Based on your last paragraph, I'm not sure that's what you want
either...

 

[dave]

Too bad. It seems like a pretty simple task. Any other workarounds?

Dave

 

[Bob Greenblatt]

Too bad. It seems like a pretty simple task. Any other workarounds?

Dave

It may be a simple task, but we did not understand what you want to do. I'm
not sure if workaround is the right term. Explain what you want, and we'll
show you how to do it.

 

[JE McGimpsey]

Too bad. It seems like a pretty simple task. Any other workarounds?

Exactly what task are you referring to? It wasn't at all clear to me...

 

[little_creature]

hiya,
basically I assumed that you have data like this
teacher - - - teacher 1
waiter - - waiter - 2

and want to replace word teacher by 1, waiter by 2...

You cannot put the *corrected* value to the same cell. So what I did I
prepare a new sheet when I

did link to the input data. See here:
http://home.tiscali.cz/zviratko/condit_formationg.xls

I used if function. Saying if in the cell is text string - (written as
"-")leave it if not place

there value find in Q row.

Hope this helps.