If Statements

[Highlander501]

Hi,

New user to the forum

I have a Excel Spreadsheet with a cell (B6) which contains the following formula:

=IF(G28="N/A","","Error - Controlled Item ! Check with Line Manager")

Cell G28 contains the following formula:

=VLOOKUP(B24,'File Name'!$A$1:$Y$330,22)

Item parts are selected in Cell B28 (from a data validation list). Depending on the part selected either N/A will be displayed in cell G28 or another item of text. If N/A is displayed the text: Error - Controlled Item ! Check with Line Manager is displayed in cell B6.

So far good! What I am now trying to do is modify the IF statement so I can get Excel to look at another cell (G33) and display the text "Error - Controlled Item ! Check with Line Manager " if either of cells G28 or G33 contain the text N/A. So far I have only managed to get Excel to display "Error - Controlled Item ! Check with Line Manager " if both cells G28 and G33 contain the text N/A using the following formula:

=IF(G28="N/A",””,IF(G33="N/A",””,"Error - Controlled Item ! Check with Line Manager"))

Rather frustrated but I'm sure there must be an easy solution. I've been looking at this for so long my head actually hurts.

Any help would be gratefully received.

Thanks,

G.

[OnErrorGoto0]

=IF(OR(G28="N/A",G33="N/A"),””,"Error - Controlled Item ! Check with Line Manager")
I think is what you want.

[Highlander501]

Hi,

Thanks for the reply. I have actually made a mistake in my original request. I only want the error message displayed if either of the two cells (G22 or G33) does not display N/A. I have entered the if(or) statement and at the moment the error message is only being displayed in B6 if both G22 and G33 contains any text other than N/A. With one of the cells containing N/A the error message is displayed.

I've now been thinking about this for far to long and my head hurts so much I can't think straight!

G.

[OnErrorGoto0]

You need to change to AND

=IF(AND(G28="N/A",G33="N/A"),””,"Error - Controlled Item ! Check with Line Manager")

[Highlander501]

You need to change to AND

=IF(AND(G28="N/A",G33="N/A"),””,"Error - Controlled Item ! Check with Line Manager")

Thanks. Changing the OR to an AND makes no difference. The error is only displayed if both G28 and G33 contain text other than N/A.

G.

[OnErrorGoto0]

That formula should only display nothing if both G28 and G33 are "N/A".

[Highlander501]

That formula should only display nothing if both G28 and G33 are "N/A".

Yes, that is correct. If both cells are N/A, nothing is displayed. What I want the formula to do is be able to display the error message if either of those cells containing anything N/A. So for example:

G28 contains N/A and G33 contains N/A - displayed nothing
G28 contains anything but N/A and G33 contains anything but N/A then the error is displayed
G28 contains N/A but G33 contains anything but N/A then the error is displayed
G28 contains anything but N/A and G33 contains N/A then the error is displayed

Sorry if I am not making my request clear, I'm generally not to bad with Excel but IF statements are my real downfall!
Hopefully the examples above will clear things up.

G.

[OnErrorGoto0]

That is what that formula should do. Only if both cells are "N/A", should nothing be displayed. For any other combination, it should show the error message.
If that is not happening, please provide a sample.

[Highlander501]

Thanks for the reply. It's almost time for the weekend here so I'll send a sample through next week.

G.

[Highlander501]

Thanks for the reply. It's almost time for the weekend here so I'll send a sample through next week.

G.

That is what that formula should do. Only if both cells are "N/A", should nothing be displayed. For any other combination, it should show the error message.
If that is not happening, please provide a sample.

Here are the sample files. The civil template is the file that should display "Error" in Cell B6. I have also attached the file that links with the civil template. I have populated the template to show that no error is returned when cells B24 and B29 are populated. If part number 3032-001 is selected in B24 the error is then returned.

I hope this helps.

G.

[OnErrorGoto0]

I am confused. You still have the OR version of the formula in there. If you switch it to AND, then it displays an error only if both cells have N/A in them, and nothing for any other combination.

[Highlander501]

I am confused. You still have the OR version of the formula in there. If you switch it to AND, then it displays an error only if both cells have N/A in them, and nothing for any other combination.

Sorry about that, I have changed the OR to and AND. I can only assume I didn't save the modified workbook before closing. The error message is now displayed when both G28 and G33 are populated. If either of them display anything other than N/A the error is displayed.

If only G28 has been populated with something other than than N/A the error is still not being displayed? (i.e there is no requirement to complete the cell which populates G33). I would like the error to be displayed in this case also.

[OnErrorGoto0]

If only G28 has been populated with something other than than N/A the error is still not being displayed?

Not for me. The error is always displayed unless both referenced cells contain N/A. If either cell is blank, the error displays.

[Highlander501]

Not for me. The error is always displayed unless both referenced cells contain N/A. If either cell is blank, the error displays.

See attached screenshot (error.doc) and a copy of the workbook with the AND statement as apposed to the OR statement. No part number has been entered in B24 therefore cell G28 contains #N/A. Cell G33 contains xxx and in this case I would like the error message to be displayed but it doesn't.

G.

[OnErrorGoto0]

The text "N/A" is not the same as the error value #N/A. I think you want

=IF(ISERROR(IF(AND(G28="N/A",G33="N/A"),"","Error")),"Error",IF(AND(G28="N/A",G33="N/A"),"","Error"))

[Highlander501]

The text "N/A" is not the same as the error value #N/A. I think you want

=IF(ISERROR(IF(AND(G28="N/A",G33="N/A"),"","Error")),"Error",IF(AND(G28="N/A",G33="N/A"),"","Error"))

Please see attachment 1. There is 1 scenario where the error is being displayed when I would like there to be no error displayed. In all other scenarios the statement above works

attachment 1.docx

[OnErrorGoto0]

Got you (I think!) - try

=IF(AND(IF(ISNA(G28),"N/A",G28)="N/A",IF(ISNA(G33),"N/A",G33)="N/A"),"","Error")

[Highlander501]

Got you (I think!) - try

=IF(AND(IF(ISNA(G28),"N/A",G28)="N/A",IF(ISNA(G33),"N/A",G33)="N/A"),"","Error")

Bingo! Houston we have lift off Works a treat. Thanks you ever so much, I really appreciate your help. One last question before I go if you don't mind. If I decide I want to expand the formula to include additional cells (G38, G43 etc etc), what would the syntax be for this? Same as before, if any cell contains anything other than N/A then the error shall be displayed.

[OnErrorGoto0]

=IF(AND(IF(ISNA(G28),"N/A",G28)="N/A",IF(ISNA(G33),"N/A",G33)="N/A"),"","Error")
the bold blue part needs to be repeated within the AND function for each additional cell.

[Highlander501]

=IF(AND(IF(ISNA(G28),"N/A",G28)="N/A",IF(ISNA(G33),"N/A",G33)="N/A"),"","Error")
the bold blue part needs to be repeated within the AND function for each additional cell.

Apoligies but I cannot get this to work. I have attached another file which explains what I'm looking for. Perhaps I should have asked for this in the first place but I thought it would be easy once I had the statement for the first few cells. My mistake.

attachment1.docx

[OnErrorGoto0]

Is that the right attachment?

[Highlander501]

Yes, that was the right attachment. The statement has not changed from the one you advised earlier (I couldn't get your suggestion to work). I have just completed 3 cells rather than the previous 2 with the explanation of what I am trying to do written below the screenshot.

[OnErrorGoto0]

=IF(AND(IF(ISNA(G28),"N/A",G28)="N/A",IF(ISNA(G33),"N/A",G33)="N/A",IF(ISNA(G38),"N/A",G38)="N/A"),"","Error")
should work.

[Highlander501]

OEGO,

Thanks very much for help, and your patience! Very much appreciated. How do I mark this thread as solved.

Best Regards,

G.

[OnErrorGoto0]

Per the FAQ link at the top of the page:

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Note: I am not sure if there is a time limit on this. If so, you may need to ask a moderator to mark it solved.