IF...VALUE formula - need help!

[Guest]

I move data (cell for cell) from EDITED to FORMAT worksheets. But in cell C,
I only want the data if the value in E equals any of the 7 options in the
formula. Otherwise I'd like 0. I'm getting 0 whether it equals the values
or not. What am I doing wrong?

I have this formula
=IF((OR(E2="In Collect",E2="In Design",E2="In Plate",E2="In Print",E2="Ready
To Collect",E2="Ready To Design",E2="Ready to Print")),VALUE(EDITED!C2),0)


TIA,
Carole O

 

[IC]

I pasted your formula into Sheet1!A1 in a new sheet, renamed Sheet2 as
EDITED, typed 27 in EDITED!C2 and typed In Collect in Sheet1!E2. A1 changed
to 27. I then changed Sheet1!E2 to something not in your list and A1 changed
to 0.

In other words, the formula works fine here. One thought, I assume E2
contains eg In Collect, not "In Collect".

Ian

 

[Guest]

Thanks, Ian - you gave me a hint. The data I want to move is Text (Yes, No).
I duplicted your suggestion (changed No to 1, and Yes to 2), and I got the
value of the cell. But - would I use 'value' to move text?

TIA,

Carole O

 

[IC]

No, VALUE changes the text equivalent of a number to that number (eg Ten
becomes 10). If you are just moving text, then get rid of the VALUE part of
the formula (ie VALUE(EDITED!C2) becomes EDITED!C2. I think this should sort
the problem out.

Ian

 

[Guest]

Thank you, Ian!! That was way too obvious!! (I'm taking an Excel VBA course
next month!!)

Carole O