Stupid Excel question of the day (if/then)

Factual Questions
1

I’m using Excel 2003 and need to make a formual to check the value of a cell, and if the value is correct, have the formula return the contents of that cell.

On Sheet1, I have

A1 B1 C1
yes blah yahoo

On sheet2, I want all of Sheet1 Row1 to appear on Sheet2 Row1 if and only if Sheet1 A1 is yes, otherwise leave it blank.
I’m trying to write the formula as:

=IF(‘Sheet1’!$A1=yes,‘Sheet1’!A1," ") which is not working. I would then copy the formula to Sheet2 B1 and B2 and if I can make it work, down to further rows.

I looked at conditional formula Excel help at: Microsoft Support which shows you how the get it to return absolutely everything but the contesnts of another cell.

Thanks in advance!

2

Do you just need to put “yes” in quotation marks in your IF formula?

This is the formula I used in Excel 2010:

=IF(Sheet1!A1=“yes”,Sheet1!A1," ")

and it works.

Not sure why your Sheet1s are in single quotes, so you could experiment with that, too.

3

I think you want this:

=IF(Sheet1!$A1=“yes”,Sheet1!A1,"")
Your problem is that you forget to put yes in quotes in your if statement.

I also think you want to have A1 as $A1 in order to only check column A for yes when checking to copy in column B. If you drag the formula above down it will look like this:

=IF(Sheet1!$A1=“yes”,Sheet1!B1,"") <— that’s what would be in cell A2

and when you drag it to the right it will look like this:

=IF(Sheet1!$A2=“yes”,Sheet1!B2,"") <— that’s what would be in cell B2

Use F4 to cycle through the dollar signs on your cell reference after you click on the cell when typing the forumla

4

Missed edit window:

what I said would appear in cell A2 is actually in cell B1

A2 would look like: =IF(Sheet1!$A2=“yes”,Sheet1!A2,"")

Sorry about that.

5

Yes, that was it… although I could have sworn I tried that, perhaps in conjunction with some other error I was making.

The single quites around the sheet name are because the actual sheet name has a space or two in it and so they’re needed to parse it correctly.

Now, extra credit: Can anyone tell me how to make the above work so all the rows for wich the “A” cell = yes are on a second sheet, but without blank rows in between for the rows for which the “A” cell =/= yes, all without sorting the source sheet by whether the “A” cell in the row =yes or not, because that source sheet needs to stay sorted by some other value.

Thank you both for your help!

6

Use a pivot table on the range on the sheet 1 values and deselect the blanks.

If you want to preserve the order of the sheet 1 values in the pivot table then have a column in Sheet 1 with the formula =row() and use that as the pivot table primary sort field

7

If you want to stick with formulas, the easiest way is to add a column to the left of the data on sheet 1, so that on Sheet 1 you now have yes, boy, and yahoo in columns B,C, and D.

Then in column A on sheet 1, use this formula:

=IF(B1=“yes”,COUNTIF($B$1:B1,“yes”),"")

and drag it down. It will generate a 1, 2, 3, etc. for each row that has a yes and will be blank for those that have no.

Then, in Sheet 2, use this formula instead of the old one:

=VLOOKUP(ROW(A1),Sheet1!$A$1:$D$100,COLUMN(B1),FALSE)

and drag it down and across. Note the table range is defined as Sheet1!$A$1:$D$100, the last number - the 100 - is how many rows are in Sheet1. I just picked 100 arbitrarily, you can set it to something real high, like a million or something, depedning on how much data you think you’ll have.

If you cannot insert a column to the left on sheet 1, there’s a way to do it with a column on the right, or by adding a third sheet, but this is the simplest approach.

8

That’s AMAZING! I’ve always wondered how to do that! Why doesn’t Excel have a good list of all shortcuts…