pulling 5 lines of info from a sheet

[stub1toe]

The trick is to ask the right question...
so here goes..

I have 6 col of information on a sheet called Data.
date,place,hours,personel,earn,tax.
In the 7th col i normally have a letter, either a "P" or an "I"
Normally i leave a letter "P" in col 7, but once a week i need to pul
5 lines of information from the information i have entered, so i mark
line with an "I".

This is what i want :
On a seperate sheet called invoice, i have 5 spaces on a formatte
page and i want to fill these 5 spaces with the information from dat
sheet.
I want "col 5" to appear in the first space if theres an "I", and i
theres a "P" to print nothing. (ONLY pull info from the line if there
an "I".
data:
date,place,hours,personel,earn,tax.
2/06/06, wor, 8, ian, 26, 4, P
3/06/06, wor, 8, ian, 23, 3, p
4/06/06, wor, 3, ian, 25, 3, p
5/06/06, wor, 8, ian, 26, 2, p
6/06/06, wor, 5, ian, 23, 3, p
7/06/06, wor, 8, ian, 28, 3, I
8/06/06, wor, 8, ian, 23, 3, p
9/06/06, wor, 8, ian, 21, 3, I
10/06/06, wor, 8, ian, 23, 3, p

So from the above:
on sheet called invoice i want

line 1 to be filled with info from 7/06/06 ie "28"
line 2 to be filled with info from 7/06/06 ie "21"

Using the if (statment , false, true) it fills the 5 lines with
blanks and i only have 5 spaces...

I have asked this question before but i cant seem to express mysel
clearly, so plz be patient..and stick with me..
fingers crossed..

Stub1to

 

[Biff]

Hi!

The trick is to ask the right question...

....and to have the "right" person(s) read it!!!!!

This table is on sheet Data, A1:G10. A1:F1 are the headers.

date,place,hours,personel,earn,tax.
2/06/06, wor, 8, ian, 26, 4, P
3/06/06, wor, 8, ian, 23, 3, p
4/06/06, wor, 3, ian, 25, 3, p
5/06/06, wor, 8, ian, 26, 2, p
6/06/06, wor, 5, ian, 23, 3, p
7/06/06, wor, 8, ian, 28, 3, I
8/06/06, wor, 8, ian, 23, 3, p
9/06/06, wor, 8, ian, 21, 3, I
10/06/06, wor, 8, ian, 23, 3, p

On your other sheet enter this formula as an array using the key combination
of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(Data!G$2:G$10,"I"),INDEX(Data!E$2:E$10,SMALL(IF(Data!G$2:G$10="I",ROW(Data!E$2:E$10)-ROW(Data!E$2)+1),ROWS($1:1))),"")

Copy down as needed.

Sample file:

Sample_extract_data.xls 14.0KB

http://cjoint.com/?hxxk7JNjAB

Biff

 

[Sasa Stankovic]

I was thinking on solution with VBA, but this seems interesting...

 

[stub1toe]

It works yeah...

Thanks Bif for your time and effort...

I got the right question and the right answer from the right person!!!

cheers mate

 

[Biff]

You're welcome. Thanks for the feedback!

Biff