More help needed .... IF statement with VLOOKUP

[TURK]

Hi all,

I'm a bit stuck, again ! hope you can help with this one, i've searched the posts, but none of the threads don't seen to achieve the result i need.

What I am trying to do is is 'import' all the jobs at "COM" status to another sheet.
You'll see in the example below I have book2

Book2.xls
	A	B	C	D	E	F
1	JobNo	Status	Date
2
3
4
5
6
7
8
9
10
COM

I want to 'import' only the jobs at "COM" status to this sheet, including obviously the job numbers and the date they were completed, from this sheet (reports).

Book2.xls
	A	B	C	D	E	F
1	jobno	customer	region	data	status	date
2	22221111	allen	south		COM	02/01/2009
3	77770000	smith	south		COM	02/01/2009
4	11112222	jones	south		PAS	02/01/2009
5	12345678	kenny	south		COM	02/01/2009
6	44444444	audran	south		PAS	02/01/2009
7	11112222	hutch	south		PAS	02/01/2009
8
9
10
reports

Thanks to a member of the forum, I can do this on the same sheet, ( sorting out all the COM status jobs ) with the following formula<B>=IF(ISNA(VLOOKUP(A3,Sky.Report!$A$381:$B$647,1,FALSE)),"",(VLOOKUP(A3,Sky.Report!$A$381:$B$647,1,FALSE)))</B>
But how do you get it to work from another sheet ?

 

[lenze]

Use Advanced Filter. Choose Filter to new location. Note, AF must originate from the destination sheet. For a more robust approach, you might use MSQuery!!
lenze

 

[TURK]

Hi lenze,

I have no idea what you have just said <img class="inlineimg" src="images/icons/icon9.gif" alt="Unhappy" border="0" />

I was hoping there was a formula that would simply lookup the job no, and return COM with an IF statement, like if job no in column A has COM in column E, and return date from colmn F.

" I'm a Noobie, please be gentle " <img class="inlineimg" src="images/icons/icon7.gif" alt="Smile" border="0" />

TURK

 

[Aladin Akyurek]

Hi lenze,

I have no idea what you have just said

I was hoping there was a formula that would simply lookup the job no, and return COM with an IF statement, like if job no in column A has COM in column E, and return date from colmn F.

" I'm a Noobie, please be gentle "

TURK

Control+shift+enter, not just enter:

=INDEX($F$2:$F$7,MATCH(1,IF($A$2:$A$7=JobNo,IF($E$2:$E$7="COM",1)),0))

 

[waldymar]

Hi man,

the previous post was correct. you don't need to copy your data to another spreadsheet. just work with the existed data. what you need is to filter your data. do this steps:
1. select your head row
2. on the main menu choose "DATA"->Filter->Autofilter. click on that.
3. from drop-down list choose "COM", or whatever you like.

tell me if it what you were looking for.