Thread: pulling 5 lines of info from a sheet

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 pull 5 lines of information from the information i have entered, so i mark a line with an "I".

This is what i want :
On a seperate sheet called invoice, i have 5 spaces on a formatted page and i want to fill these 5 spaces with the information from data sheet.
I want "col 5" to appear in the first space if theres an "I", and if theres a "P" to print nothing. (ONLY pull info from the line if theres 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 a blanks and i only have 5 spaces...

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

Stub1toe

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

"stub1toe" <stub1toe.2beqpt_1153673709.2396@excelforum-nospam.com> wrote in
message news:stub1toe.2beqpt_1153673709.2396@excelforum-nospam.com...
>
> 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 pull
> 5 lines of information from the information i have entered, so i mark a
> line with an "I".
>
> This is what i want :
> On a seperate sheet called invoice, i have 5 spaces on a formatted
> page and i want to fill these 5 spaces with the information from data
> sheet.
> I want "col 5" to appear in the first space if theres an "I", and if
> theres a "P" to print nothing. (ONLY pull info from the line if theres
> 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 a
> blanks and i only have 5 spaces...
>
> I have asked this question before but i cant seem to express myself
> clearly, so plz be patient..and stick with me..
> fingers crossed..
>
> Stub1toe
>
>
> --
> stub1toe
> ------------------------------------------------------------------------
> stub1toe's Profile:
> http://www.excelforum.com/member.php...o&userid=34425
> View this thread: http://www.excelforum.com/showthread...hreadid=564108
>

I was thinking on solution with VBA, but this seems interesting...
"Biff" <biffinpitt@comcast.net> wrote in message
news:uPl7w3prGHA.1140@TK2MSFTNGP05.phx.gbl...
> 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
>
> "stub1toe" <stub1toe.2beqpt_1153673709.2396@excelforum-nospam.com> wrote
> in message news:stub1toe.2beqpt_1153673709.2396@excelforum-nospam.com...
>>
>> 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 pull
>> 5 lines of information from the information i have entered, so i mark a
>> line with an "I".
>>
>> This is what i want :
>> On a seperate sheet called invoice, i have 5 spaces on a formatted
>> page and i want to fill these 5 spaces with the information from data
>> sheet.
>> I want "col 5" to appear in the first space if theres an "I", and if
>> theres a "P" to print nothing. (ONLY pull info from the line if theres
>> 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 a
>> blanks and i only have 5 spaces...
>>
>> I have asked this question before but i cant seem to express myself
>> clearly, so plz be patient..and stick with me..
>> fingers crossed..
>>
>> Stub1toe
>>
>>
>> --
>> stub1toe
>> ------------------------------------------------------------------------
>> stub1toe's Profile:
>> http://www.excelforum.com/member.php...o&userid=34425
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=564108
>>
>
>

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

You're welcome. Thanks for the feedback!

Biff

"stub1toe" <stub1toe.2bgbw3_1153747806.8081@excelforum-nospam.com> wrote in
message news:stub1toe.2bgbw3_1153747806.8081@excelforum-nospam.com...
>
> 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
>
>
> --
> stub1toe
> ------------------------------------------------------------------------
> stub1toe's Profile:
> http://www.excelforum.com/member.php...o&userid=34425
> View this thread: http://www.excelforum.com/showthread...hreadid=564108
>