How to: If cell E1 says YES, add the information in A1 into sheet 2 A1? But what if NO?

[tv_helge]

Hi
I am wondering if anyone knows the formula on this:

If cell E2 says YES, add the information in A1 into sheet 2 - A1?

That is probably not the easy part. But then it gets a bit tricky. If all answers were YES, then no problem. However, if i.e. E1-E2 is YES, E3 is NO, E4-E5 is YES. Is it possible to tell excel that if NO somewhere, skip that row in the other sheet?

Example:
SHEET 1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]100
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]VW
[/TD]
[TD="align: center"]5000
[/TD]
[TD="align: center"]YES
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]101
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]FIAT
[/TD]
[TD="align: center"]3000
[/TD]
[TD="align: center"]YES
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]102
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]BMW
[/TD]
[TD="align: center"]1500
[/TD]
[TD="align: center"]NO
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]103
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]KIA
[/TD]
[TD="align: center"]2000
[/TD]
[TD="align: center"]YES
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]104
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]AUDI
[/TD]
[TD="align: center"]2500
[/TD]
[TD="align: center"]YES
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]105
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]VOL..
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]YES
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]100
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]VW
[/TD]
[TD="align: center"]5000
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]101
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]FIAT
[/TD]
[TD="align: center"]3000
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]103
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]KIA
[/TD]
[TD="align: center"]2000
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]104
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]AUDI
[/TD]
[TD="align: center"]2500
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]105
[/TD]
[TD="align: center"]CAR
[/TD]
[TD="align: center"]VOL..
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

As you can see, what I am trying to do is that if column E says "YES", the data goes to sheet 1 as shown above. However, if column E shows "NO", I would not only like it to not appear on sheet 1, but to remove that row so that I do not get a lot of open cells or cells with a "0" on sheet 1. Also, if I add any information in row 6 on sheet 1 when E6=YES, I would like that information to be added below the last row in sheet 2 (which in this case is row 5).

Anyone got an idea?

regards
Helge

 

[Eric W]

Sure!

With this kind of thing, it's usually best to have a helper column on Sheet2, like this:

	A	B	C	D	E
CAR	VW
CAR	FIAT
CAR	KIA
CAR	AUDI
CAR	VOL..

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]3000[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]103[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]104[/TD]

[TD="align: right"]2500[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]105[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]=IF($E1<>"",INDEX(Sheet1!A$1:A$20,$E1),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF(Sheet1!$E$1:$E$20="YES",ROW($E$1:$E$20)-ROW($E$1)+1),ROWS($E$1:$E1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





The formula in E1 looks for the rows on Sheet1 with YES in them. It's an array formula, make sure you enter it with Control+Shift+Enter. Then the formula in A1, which you copy to B1:D1, then down the columns, is just a basic index formula using the row number from column E.

Hope this helps!

 

[tv_helge]

Hi Eric
Thank you very much for your help. I am very much new to using more advanced excel formulas, so I will have to try this out a bit first, to see if I get it.

regards
Helge

 

[Eric W]

Hi Helge,

Sure, try it out. Let us know if you have any questions.

 

[tv_helge]

Hi Eric
I have almost got it. Or, I got the first row to do it. But what do I change in the formula on column E2-E5 etc on sheet 2?

regards
Helge

 

[Eric W]

No changes. Just put the E1 formula in, then copy it, and paste it to E2:E5, etc.

 

[tv_helge]

Hmmm... When I did that, it shows up with the same on all columns:

Sheet 2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]100
[/TD]
[TD]CAR
[/TD]
[TD]VW
[/TD]
[TD]5000
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]100
[/TD]
[TD]CAR
[/TD]
[TD]VW
[/TD]
[TD]5000
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]100
[/TD]
[TD]CAR
[/TD]
[TD]VW
[/TD]
[TD]5000
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]100
[/TD]
[TD]CAR
[/TD]
[TD]VW
[/TD]
[TD]5000
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]100
[/TD]
[TD]CAR
[/TD]
[TD]VW
[/TD]
[TD]5000
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]100
[/TD]
[TD]CAR
[/TD]
[TD]VW
[/TD]
[TD]5000
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

That is when I copied the formula on E1 on sheet 2 to E2, E3 etc...

{=IFERROR(SMALL(IF(Sheet1!$E$1:$E$20="YES",ROW($E$1:$E$20)-ROW($E$1)+1),ROWS($E$1:$E1)),"")}

I thought maybe I had to change one or two numbers inside? I would need row 2 to get info from row 2 on Sheet 1, row 3 from row 3 etc...

regards
Helge

 

[Eric W]

Hmm. Did you enter the formula in the formula bar in E1, then press Control+Shift+Enter? Then did you enter the formula in the formula bar in E2, and so on? If so, that would explain why you're getting the same result. There are a couple ways to "copy" the formula that should work.

Enter the formula in E1 as you did. Now select the E1 cell. The cell is surrounded by a box, and at the bottom right is a tiny square. Click on that, hold the mouse button down, and drag the mouse down the column as far as you need.

Or select E1, press Control-C, then select the cells you want to copy it to, and press Control-V. Those both should work. Either way is much quicker/safer than manually entering all the formulas.

Now if you look at the E2 cell, the formula should look like:

{=IFERROR(SMALL(IF(Sheet1!$E$1:$E$20="YES",ROW($E$1:$E$20)-ROW($E$1)+1),ROWS($E$1:$E2)),"")}

The only thing that should be different is the red 2. All the other range references have the $ in front of them, meaning "absolute reference". Since the 2 did not have one, it meant "relative reference", meaning that Excel will adjust that value relative to the cell you copied it from.

The same thing is used in the A1 formula, so make sure you copy that the same way.

 

[tv_helge]

Hi, yes I did in fact do it exactly like you write. I tried both actually. And the formula is exactly like you write for E2 etc. But it still shows the same in all columns and rows...only the information from row 1 on sheet 1... I tried a few things. If I write "NO" in one of the E columns on sheet 1, on sheet 2 it appears with the correct information when it should have been removed, and the lines were it says YES still appears with information from row 1. If you understand what I am trying to explain...

Correction; I tried one more time, and now nothing appears on sheet 2. I have now double checked the formulas, and it is exactly like you have written. I really don't get why it's not working. Frustrating...

regards
Helge

 

[tv_helge]

And triple checked, now it is back to option 1; where NO is showing in the list, but should have been removed, and YES is only refering to row 1 on sheet 1... Would you be able to send me a new excel sheet showing that it works? Then I can check to see if there is anything different from mine...?

Helge