Conditional format formula find/replace

[dzelnio]

My rows go like this:
A B C D E F
Job name Jan Feb Mar Apr Page number
ABC job - ABC - ABC 8

I want a formula that says anything that is not a "-" or "0" or blank
will be replaced with the page number (in this case an 8).

The result would be:
Job name Jan Feb Mar Apr Page number
ABC job - 8 - 8 8

Thanks in advance!

Dave

 

[Bob Greenblatt]

My rows go like this:
A B C D E F
Job name Jan Feb Mar Apr Page number
ABC job - ABC - ABC 8

I want a formula that says anything that is not a "-" or "0" or blank
will be replaced with the page number (in this case an 8).

The result would be:
Job name Jan Feb Mar Apr Page number
ABC job - 8 - 8 8

Thanks in advance!

Dave

Dave, Are you aware, that a formula can only change the value of the cell in
which it resides. It can not change the value of any other cell. To do that,
you'll need VBA. You could construct an if statement for each of the month
cells, to generate what you want, but unless I have some more info, I can't
help. I need to know what conditions create the - and the job number.

 

[dzelnio]

Each month is assigned a job. If that job is not due in the month,
it's cooresponding cell gets a "-". The operator might miss a hyphen
which would result in a blank cell or a "0" from the paste special.

An "if" value is fine as long as it works for cells that are done by
paste special

IF A1 does not include "-, 0, "blank" " then A1=B1

Does that make sense?

Dave

 

[jpdphd]

My rows go like this:
A B C D E F
Job name Jan Feb Mar Apr Page number
ABC job - ABC - ABC 8

I want a formula that says anything that is not a "-" or "0" or blank
will be replaced with the page number (in this case an 8).

The result would be:
Job name Jan Feb Mar Apr Page number
ABC job - 8 - 8 8

Thanks in advance!

Dave

Dave,
Your new values for the months can be in columns G, H, I, J
Assume you are starting in row 2. Into G2 type:
=IF(OR(B2="",B2="-",B2=0),IF(B2="","",B2),$F2)
Copy the formula to the other cells.
jpdphd

 

[dzelnio]

That formula works perfect!

Thanks

Dave

Dave,
Your new values for the months can be in columns G, H, I, J
Assume you are starting in row 2. Into G2 type:
=IF(OR(B2="",B2="-",B2=0),IF(B2="","",B2),$F2)
Copy the formula to the other cells.
jpdphd