Selective cell changes

[Colin Hayes]

HI

In column Y I have a row of numbers.

I need to change them so that the cells which are 0 stay the same , and
cells with numbers greater than 0 in them are replaced with the word
'Active' .

Can someone help with this?

Thanks.

 

[Guest]

One way - use an adjacent helper col

Assuming numbers running in Y2 down
Put in Z2: =IF(Y2>0,"active",Y2)
Copy down. Kill the formulas in col Z with an "in-place" copy>paste special
as values. Delete col Y.

If you want any blank cells in col Y to return as "blanks", ie:""
then use instead in Z2:
=IF(Y2="","",IF(Y2>0,"active",Y2))

 

[Colin Hayes]

One way - use an adjacent helper col

Assuming numbers running in Y2 down
Put in Z2: =IF(Y2>0,"active",Y2)
Copy down. Kill the formulas in col Z with an "in-place" copy>paste special
as values. Delete col Y.

If you want any blank cells in col Y to return as "blanks", ie:""
then use instead in Z2:
=IF(Y2="","",IF(Y2>0,"active",Y2))

Max

excellent , thanks. That fixed it.

Very grateful.



Best Wishes


Colin

 

[Max]

Glad it did, Colin.
Thanks for feedback.

 

[Colin Hayes]

Max

I've just had a little extra problem.

Could I use your formula to replace cells in the column with no content
to read 'NULL' , with cells having content remaining the same?


Best Wishes


Colin

 

[Dave Peterson]

You have a reply at your next post.

Max

I've just had a little extra problem.

Could I use your formula to replace cells in the column with no content
to read 'NULL' , with cells having content remaining the same?

Best Wishes

Colin

 

[Max]

Instead of

=IF(Y2="","",IF(Y2>0,"active",Y2))

you could use:
=IF(Y2="","NULL",IF(Y2>0,"active",Y2))