IF...VALUE formula - need help!
 

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

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

"Carole O" wrote in message
...
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

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" wrote:

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

"Carole O" wrote in message
...
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

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

"Carole O" wrote in message
...
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" wrote:

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

"Carole O" wrote in message
...
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

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

Carole O

"IC" wrote:

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

"Carole O" wrote in message
...
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" wrote:

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

"Carole O" wrote in message
...
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