Solved I'm having a nested IF/AND issue

February 19, 2013 at 12:11:47
Specs: Windows XP
I am trying to get a group of nested IF functions to use each subsequent AND argument as the FALSE for the previous AND argument in the string. This is what I have so far, and all I get is a FALSE after the first condition isn't TRUEN any longer:

=IF(AND(E2="X", F2="X", G2="X", H2="X"), "X:\Engineering\Projects (Active)\Real Go\UL.bmp", IF(AND(E2=" ",F2="X",G2="X",H2="X"),"X:\Engineering\Projects (Active)\Real Go\CSA.bmp", IF(AND(E2=" ", F2=" ",G2="X",H2="X"),"X:\Engineering\Projects (Active)\Real Go\CE.bmp", IF(AND(E2=" ", F2=" ", G2=" ", H2="X"),"X:\Engineering\Projects (Active)\Real Go\UR.bmp", IF(AND(E2=" ", F2=" ", G2=" ", H2=" ")," ")))))

What I am trying to accomplish is that based on the entering of an "X" into column E, F, G, and H, the bitmaps will populate into columns J, K, L, and M. If one of the E-H columns is left empty, I want the J-M columns to fill to the left and leave no repeat bitmaps.


See More: Im having a nested IF/AND issue

Report •


✔ Best Answer
February 19, 2013 at 16:13:27
Try this as an alternative.

It may look complicated but go step by step.

We are going to use Data Valadation and some drop box's

First thing were going to do is to change your Column headings like below:

      E           F          G          H
1) Diagram 1   Diagram 2  Diagram 3  Diagram 4

Next, somewhere on your sheet, where they will be out of the way
like column X, make a list of your old heading:

    X
1) CE
2) CSA
3) UL
4) UR

Next, goto cell E2
Click on the Data tab.
Click on the Data Validation option from the ribbon to open the menu.
Click on Settings tab in the dialog box.
From the Allow menu choose List.
Click on the Source line in the dialog box.
Select your cells $X$1 - $X$4 in the spreadsheet
or simply enter the formula: =$X$1:$X$4
Note the Dollar Signs, they "anchor" the list and are required.
Click OK in the dialog box.
A down arrow should appear next to cell E2.
When you click on the arrow the drop down list should open to display the four selections.

Next, Copy cell E2 and Paste to cells F2, G2, H2,
your Diagram 2, 3, and 4.
All four cells should now have a down arrow indicating a drop box.

Next, in cell I2 enter the formula:

=IF(E3="","","X:\Engineering\Projects (Active)\Real Go\"&E3&".bmp")

Next, Copy cell I2 and Past to cells J2, K2, L2
These are where you path to the BMPs will appear.

Your finished.

Now when you use the drop list under Diagram 1 and select your Code letters
cell I2 will show the first BMP
Use the drop list under Diagram 2 and select your Code letters
and cell J2 will show the second BMP
etc. etc.

See how that works for you.

MIKE

http://www.skeptic.com/



#1
February 19, 2013 at 12:55:12
The first thing I see is that you have the construct:

E2=" "

This checks if cell E2 has a single space character.
This does not check for a blank cell,

If you want to check for a blank cell, then: E2="" <<<No Space between quotes

MIKE

http://www.skeptic.com/


Report •

#2
February 19, 2013 at 12:57:25
Great! I never knew that. I assumed I had my equation checking for blank cells. I will try that and see what that gains me!

Report •

#3
February 19, 2013 at 13:20:11
That is a great start towards my end goal I think. I still can't seem to get my column results to auto-shift to the left so as to not have the duplicate answers. I need this to happen in this fashion because I am having Bartender label printing software reference this Excel spreadsheet as the database for printing agency approval stamps on labels. I wish I could attach a screen shot or do a better job of describing my overall issue, but the tip about the "" vs. " " was awesome. So thanks for that!

Report •

Related Solutions

#4
February 19, 2013 at 13:43:57
If one of the E-H columns is left empty, I want the J-M columns to fill to the left and leave no repeat bitmaps.

Don't understand what your trying to do.

Post an example of your spreadsheet with Row Numbers and Column Letters,
after you read this How-To:

http://www.computing.net/howtos/sho...

Which will show you how to keep everything looking neat.

MIKE

http://www.skeptic.com/


Report •

#5
February 19, 2013 at 13:51:47
Part Number	Voltage Rating	Current Rating	Connection Diagram	UL	CSA	CE	UR	Connection Diagram IF       	                        UL IF	                                          CSA IF	                                        CE IF	                                            UR IF
123456	        120VAC	        10A @ 240VAC	          1         	X	X	X	X	X:\Engineering\Projects (Active)\Real Go\CD1.bmp	X:\Engineering\Projects (Active)\Real Go\UL.bmp	  X:\Engineering\Projects (Active)\Real Go\CSA.bmp	X:\Engineering\Projects (Active)\Real Go\CE.bmp	    X:\Engineering\Projects (Active)\Real Go\UR.bmp
234567	        230VAC	        5A resistive @ 240VAC	  2	        X			X	X:\Engineering\Projects (Active)\Real Go\CD2.bmp	X:\Engineering\Projects (Active)\Real Go\UL.bmp                                                                                                             X:\Engineering\Projects (Active)\Real Go\UR.bmp
345678	        24VAC	        15A @ 240VAC	          3	        X		X	X	X:\Engineering\Projects (Active)\Real Go\CD3.bmp	X:\Engineering\Projects (Active)\Real Go\UL.bmp	                                                      	X:\Engineering\Projects (Active)\Real Go\CE.bmp	    X:\Engineering\Projects (Active)\Real Go\UR.bmp
456789	        24-28VDC        20A @ 240VAC	          4	        X	X			X:\Engineering\Projects (Active)\Real Go\CD4.bmp	X:\Engineering\Projects (Active)\Real Go\UL.bmp	  X:\Engineering\Projects (Active)\Real Go\CSA.bmp	 	 


Report •

#6
February 19, 2013 at 14:14:25
That may make more sense. The blank spaces under "CSA IF" and "CE IF" in the second row, ideally I would like those to be gone and then the results in the "UR IF" column moved to show in the "CSA IF" column of that same row.

Hope that helps. The advice and insight is greatly appreciated still!


Report •

#7
February 19, 2013 at 14:31:43
Dear mmcconaghy,

The thoughts that propagated in my head after reading you initial comment about the "" vs " ", got me to thinking and I believe I figured out my problem. The solution seems to be to set the arguments to be as such from UL IF to UR IF columns:

=IF(AND(E2="X", F2="X", G2="X", H2="X"), "X:\Engineering\Projects (Active)\Real Go\UL.bmp", IF(AND(E2="",F2="X",G2="X",H2="X"),"X:\Engineering\Projects (Active)\Real Go\CSA.bmp", IF(AND(E2="", F2="",G2="X",H2="X"),"X:\Engineering\Projects (Active)\Real Go\CE.bmp", IF(AND(E2="", F2="", G2="", H2="X"),"X:\Engineering\Projects (Active)\Real Go\UR.bmp", IF(AND(E2="", F2="", G2="", H2=""),"")))))

=IF(AND(E2="X", F2="X", G2="X", H2="X"), "X:\Engineering\Projects (Active)\Real Go\CSA.bmp", IF(AND(E2="",F2="X",G2="X",H2="X"),"X:\Engineering\Projects (Active)\Real Go\CE.bmp", IF(AND(E2="", F2="",G2="X",H2="X"),"X:\Engineering\Projects (Active)\Real Go\UR.bmp", IF(AND(E2="", F2="", G2="", H2="X"),"", IF(AND(E2="", F2="", G2="", H2=""),"")))))

=IF(AND(E2="X", F2="X", G2="X", H2="X"), "X:\Engineering\Projects (Active)\Real Go\CE.bmp", IF(AND(E2="",F2="X",G2="X",H2="X"),"X:\Engineering\Projects (Active)\Real Go\UR.bmp", IF(AND(E2="", F2="",G2="X",H2="X"),"", IF(AND(E2="", F2="", G2="", H2="X"),"", IF(AND(E2="", F2="", G2="", H2=""),"")))))

=IF(AND(E2="X", F2="X", G2="X", H2="X"), "X:\Engineering\Projects (Active)\Real Go\UR.bmp", IF(AND(E2="",F2="X",G2="X",H2="X"),"", IF(AND(E2="", F2="",G2="X",H2="X"),"", IF(AND(E2="", F2="", G2="", H2="X"),"", IF(AND(E2="", F2="", G2="", H2=""),"")))))

I had to change my results of the different "X" positions, so that more of the results were "" instead. I actually was drawing up a truth table for an AND gate when this hit me.

APPRECIATE THE HELP SOOO MUCH!! THANKS A TON!!


Report •

#8
February 19, 2013 at 14:52:27
LOL. After I thought about this more, I realized that these arguments only work properly if the "X"s are removed sequentially from E2-H2. If only F2, for example is made to a blank cell, FALSE. More AND scenarios to follow on my spreadsheet I guess. But at least after three days of pondering, I have made some progress!!

Report •

#9
February 19, 2013 at 15:26:16
I may have a simpler solution.

But, first a few questions.

I am guessing that your BMP files are all named for there corresponding Columns:

UL = X:\Engineering\Projects (Active)\Real Go\UL.bmp

CSA = X:\Engineering\Projects (Active)\Real Go\CSA.bmp

CE = X:\Engineering\Projects (Active)\Real Go\CE.bmp

UR = X:\Engineering\Projects (Active)\Real Go\UR.bmp

and that X:\Engineering\Projects (Active)\Real Go\CD1.bmp is always displayed, if any of the others are selected.

Is that correct?

I noticed that you are giving a complete path.
Are you expecting to display these BMP files
from within Excel or are you simply telling the user where the files are located?

MIKE

http://www.skeptic.com/


Report •

#10
February 19, 2013 at 15:34:38
You are correct in assuming that the BMP files are named for their corresponding columns.

The CD1, CD2, CD3, and CD4 BMPs vary based on a different criteria in my spreadsheet (sorry, should have left that part off and stuck with just the pertinent info). It is independent of what happens with UL, CSA, CE, and UR BMPs though.

The complete path is given because of the Bartender label software that is using the spreadsheet as its database. It allows Bartender to locate the actual BMP file for printing. Unfortunately, I cannot put the BMPs in the same folder as the spreadsheet so I could just say "\UL.bmp", etc.


Report •

#11
February 19, 2013 at 16:13:27
✔ Best Answer
Try this as an alternative.

It may look complicated but go step by step.

We are going to use Data Valadation and some drop box's

First thing were going to do is to change your Column headings like below:

      E           F          G          H
1) Diagram 1   Diagram 2  Diagram 3  Diagram 4

Next, somewhere on your sheet, where they will be out of the way
like column X, make a list of your old heading:

    X
1) CE
2) CSA
3) UL
4) UR

Next, goto cell E2
Click on the Data tab.
Click on the Data Validation option from the ribbon to open the menu.
Click on Settings tab in the dialog box.
From the Allow menu choose List.
Click on the Source line in the dialog box.
Select your cells $X$1 - $X$4 in the spreadsheet
or simply enter the formula: =$X$1:$X$4
Note the Dollar Signs, they "anchor" the list and are required.
Click OK in the dialog box.
A down arrow should appear next to cell E2.
When you click on the arrow the drop down list should open to display the four selections.

Next, Copy cell E2 and Paste to cells F2, G2, H2,
your Diagram 2, 3, and 4.
All four cells should now have a down arrow indicating a drop box.

Next, in cell I2 enter the formula:

=IF(E3="","","X:\Engineering\Projects (Active)\Real Go\"&E3&".bmp")

Next, Copy cell I2 and Past to cells J2, K2, L2
These are where you path to the BMPs will appear.

Your finished.

Now when you use the drop list under Diagram 1 and select your Code letters
cell I2 will show the first BMP
Use the drop list under Diagram 2 and select your Code letters
and cell J2 will show the second BMP
etc. etc.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#12
February 20, 2013 at 07:39:22
That worked great Mike! And waaaaay less typing to do. I think now, the book can be closed on this one.

Thanks again for all of your effort


Report •


Ask Question