Quick Excel Question.....

[Sushi]

Hi, could do with some help if anyone can think how to do this. It should be simple but I can't get it.

I have a sheet with a list of names and a Yes/No drop down box in each row with the name.

I have a seperate sheet in the same workbook where I want to use the same list but only pull through the "Yes" rows. Easy enough to do with an IF statement filled down but I don't want blank rows where the "No" rows appear.

How can I get excel to make each row of the new sheet contain relevant data?

[inspectercoley]

Use and auto filter and then just copy and paste that.

Data filter auto filter and then choose yes for that column.

[SiE]

use a direct cell link to the cells on the first sheet then apply a filter for yes

[Sushi]

Excellent guys. Cheers.

[Karoline]

use a direct cell link to the cells on the first sheet then apply a filter for yes

Yes, this is what i was going to say, just studied this too on Tue night, lol

[Sushi]

Slight problem with this guys - the filter works ok but it's not dynamic. So if I apply the filter for the info to be pulled through to the other sheet and someone goes into the first sheet and changes the "yes" to a "no" after that time, sheet 2 won't automatically update unless the filter is reapplied. Any thoughts?

In addition to that, the filter function just hides the rows that you don't want to see so if you made a direct cell reference from another sheet then the cell it refers to will still be there although hidden.

[SiE]

you could probably write some vba code to refresh the second sheet after entry on the first screen.

[Bodman]

You need to use Vlookup. Il try to explain

Your page would look like this



Column G is your names
Column I is your drop down boxes (Yes/NO)
Column F is a copy of colmn I (=i) (Vlookup uses left hand column, you can hide this column later)


On your second page you would need a Vlookup command like this

=VLOOKUP("Yes",F1:G1,2,FALSE)



"Yes" is what your searching for
F1:G1 is the table in which you are searching (Do each line as its own table)
2 If it finds "Yes" then output the data from 2 columns across (the name)
FALSE if it doenst find "Yes" stop (It outputs N/A)

Hope this help ya


//Bod

[jaguar982]

Yes m8 vlookup should sort it

jag

[Sushi]

Cheers folks. Much obliged.

[inspectercoley]

You will still have a similar problem to before tho cos you will have to then re-apply the filters every time. And either start by making the lookup much bigger than it need to be and just add to it. or if its the correct size then every time you add a line it will then need to be redone. (well add x to the cell number reference where that is the number of lines added.

[Sushi]

About the VLOOKUP command - maybe I'm missing something but from what I can see this will give me the same problem as I had originally when using the "IF" statement to detect the "Yes" lines. i.e. I will get blank rows (or in this case #N/A) for the rows which have a "No".

eg. in the example above, lets say Frank and Charlie were actually a "No" instead of all being "Yes" this would leave a #N/A in the calculated column.

What I'm trying to create in my second sheet is the truncated list containing only the "Yes" people rather than a list which is the same in size but with gaps (or #N/A) in it.

What do you guys think am I missing something here?

[Sushi]

Ok, I think I've got this working now. I used VLOOKUP on the whole table and added a column where the cell contained the cell reference for the one below. I then pulled that info through along with the persons name etc. I then used that cell reference as the starting point for the next VLOOKUP so the table size is reducing each time it searches through for "Yes".

Only problem now really is that the cells at the bottom of the calculated table all contain #N/A until they are used. This doesn't affect the functionality of the sheet but doesn't look very pretty.

Does anyone know if you can get Excel to ignore what it perceives to be formula errors and just leave the cell blank??

EDIT - Got that sorted too. You can use the ISNA command to suppress the error messages. DONE!