Need help with formula

  • Thread starter Thread starter Connie
  • Start date Start date
C

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
 
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
 
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.
 
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
 
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,
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
 
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
Click to expand...

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
 
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
Click to expand...
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.
 
Back
Top