Need help with formula

[Connie]

I have this formula on worksheet 4, which is working fine:

=IF('SHEET 1'!AH16<>"",'SHEET 1'!AH16,IF('SHEET 2'!
AH16<>"",'SHEET 2'!AH16,IF('SHEET 3'!AH16<>"",'SHEET 3'!
AH16,"")))

Now I need to add to that formula that if AH16 = OV, then
to ignore it. This applies to each sheet.

AH16 on all sheets could be blank, or could be P, or B, or
OV. If OV is in AH16, I don't want anything put in the
cell on worksheet 4.

Hope I'm making this clear.

Thank you.
Connie

 

[Mark Graesser]

Connie
If I read you right you can use the OR function

=IF(OR('SHEET 1'!AH16<>"",'SHEET 1'!AH16="OV"),'SHEET 1'!AH16
IF(OR('SHEET 2'!AH16<>"",'SHEET 2'!AH16="OV"),'SHEET 2'!AH16
IF(OR('SHEET 3'!AH16<>"",'SHEET 3'!AH16="OV"),'SHEET 3'!AH16,""))

Hope this is what you are looking for

Good Luck
Mark Graesse
(e-mail address removed)

----- Connie wrote: ----

I have this formula on worksheet 4, which is working fine

=IF('SHEET 1'!AH16<>"",'SHEET 1'!AH16,IF('SHEET 2'
AH16<>"",'SHEET 2'!AH16,IF('SHEET 3'!AH16<>"",'SHEET 3'
AH16,""))

Now I need to add to that formula that if AH16 = OV, then
to ignore it. This applies to each sheet.

AH16 on all sheets could be blank, or could be P, or B, or
OV. If OV is in AH16, I don't want anything put in the
cell on worksheet 4

Hope I'm making this clear

Thank you.
Conni

 

[James]

Replace: 'SHEET 1'!AH16<>""

 

[James]

I don't see my first response yet, but Mark has it right.
It should read ...="OV", not ...<>"OV", which I believe is
what I had said. Hopefully you also see Mark's response
before mine, and my first post doesn't mislead you.

 

[Connie]

It doesn't work. OV, which is on worksheet1 in AH16 is
carrying over to the cell on worksheet 4. The cell on
worksheet 4 should be blank if it's OV on worksheet 1 (as
well as the other sheets).

Connie

 

[Connie]

It doesn't work. OV, which is on worksheet1 in AH16 is
carrying over to the cell on worksheet 4. The cell on
worksheet 4 should be blank if it's OV on worksheet 1 (as
well as the other sheets).

Connie

 

[Connie]

Mark's doesn't work either.

Connie

 

[Mark Graesser]

Connie,
I misunderstood what you wanted. I thought that if OV was on a sheet, you wanted to go to the next sheet. If I understand now, you want sheet 4 to be blank if OV shows up on sheet 1.

=IF('SHEET 1'!AH16="OV","",
IF('SHEET 1'!AH16<>"",'SHEET 1'!AH16,
IF('SHEET 2'!AH16<>"",'SHEET 2'!AH16,
IF('SHEET 3'!AH16<>"",'SHEET 3'!AH16,""))))

Or if you want sheet 4 to be blank if OV is on any sheet:

=IF(OR('SHEET 1'!AH16="OV",'SHEET 2'!AH16="OV",'SHEET 3'!AH16="OV"),"",
IF('SHEET 1'!AH16<>"",'SHEET 1'!AH16,
IF('SHEET 2'!AH16<>"",'SHEET 2'!AH16,
IF('SHEET 3'!AH16<>"",'SHEET 3'!AH16,""))))

Good Luck,
Mark Graesser
(e-mail address removed)


----- Connie wrote: -----

It doesn't work. OV, which is on worksheet1 in AH16 is
carrying over to the cell on worksheet 4. The cell on
worksheet 4 should be blank if it's OV on worksheet 1 (as
well as the other sheets).

Connie

 

[Ron Rosenfeld]

I have this formula on worksheet 4, which is working fine:

=IF('SHEET 1'!AH16<>"",'SHEET 1'!AH16,IF('SHEET 2'!
AH16<>"",'SHEET 2'!AH16,IF('SHEET 3'!AH16<>"",'SHEET 3'!
AH16,"")))

Now I need to add to that formula that if AH16 = OV, then
to ignore it. This applies to each sheet.

AH16 on all sheets could be blank, or could be P, or B, or
OV. If OV is in AH16, I don't want anything put in the
cell on worksheet 4.

Hope I'm making this clear.

Thank you.
Connie

I think this will do what you want:

=IF(Sheet1!AH16<>"OV",T(Sheet1!AH16),
IF(Sheet2!AH16<>"OV",T(Sheet2!AH16),
IF(Sheet3!AH16<>"OV",T(Sheet3!AH16),"")))


--ron

 

[Connie]

This formula works perfectly. Thank you! Sorry I didn't
make myself very clear initially.

Connie

-----Original Message-----
Connie,
I misunderstood what you wanted. I thought that if OV

was on a sheet, you wanted to go to the next sheet. If I
understand now, you want sheet 4 to be blank if OV shows
up on sheet 1.

 

[Mark Graesser]

Happy to help.

Merry Christmas,
Mark Graesser