pulling 5 lines of info from a sheet

  • Thread starter Thread starter stub1toe
  • Start date Start date
S

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
 
Hi!
The trick is to ask the right question...
Click to expand...

....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
Click to expand...

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
 
:) 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
 
Back
Top