array formulas

Bob,

Is it possible to search for all salesmen that are not
Bob, Jim or Dave???
(thanks)

>-----Original Message-----
>Just a couple of points, this formula
>
>=INDEX(Sheet1!B1:B100,A1)
>
>should be abso9lute for copying down
>
>=INDEX(Sheet1!$B$1:$B$100,A1)
>
>and you get extra data in the same sort of way
>
>=INDEX(Sheet1!$D$1:$D$100,A1)
>
>--
>
>HTH
>
>RP
>(remove nothere from the email address if mailing direct)
>
>
>"johnT" <anonymous@discussions.microsoft.com> wrote in
message
>news:222101c532cb$be6f1cf0$a601280a@phx.gbl...
>> yes, this works great...thanks again for all your help!!
>> >-----Original Message-----
>> >John,
>> >
>> >I think a bit more information would help, but here is
a
>> starter
>> >
>> >Assuming the salesmen are list in A1:A100 of sheet1
>> >
>> >on Sheet2, add this to A1, assuming a salseman of Bob
>> >
>> >=IF(ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1>COUNTIF(Sheet1!
>> $A$1:$A$100,"Bob"),"",S
>> >MALL(IF(Sheet1!$A$1:$A$100="Bob",ROW(Sheet1!
>> $A$1:$A$100),""),ROW(Sheet1!A1)-
>> >ROW(Sheet1!$A$1)+1))
>> >
>> >and copy down to A100 This is an array formula so
commit
>> with
>> >Ctrl-Shift-Enter.
>> >
>> >You now have the row numbers of the matching salesmen
on
>> sheet1 with no
>> >gaps. You then just get the data like so in B1
>> >
>> >=INDEX(Sheet1!B1:B100,A1)
>> >
>> >etc.
>> >
>> >--
>> >
>> >HTH
>> >
>> >RP
>> >(remove nothere from the email address if mailing
direct)
>> >
>> >
>> >"johnT" <anonymous@discussions.microsoft.com> wrote in
>> message
>> >news:194301c53269$73231100$a401280a@phx.gbl...
>> >> I have a rather large spreadsheet listing salesmen,
>> >> customers, locations etc. I would like to create
>> separate
>> >> worksheets for each salesman listing only data
related
>> to
>> >> that salesman...on each worksheet i want to referance
>> the
>> >> master worksheet, how can i do this without having a
>> large
>> >> group of blank rows??? I think this may be an
>> application
>> >> for array formulas but i need some help.
>> >>
>> >> thanks in advance....
>> >
>> >
>> >.
>> >
>
>
>.
>

Bit clumsy, but can do it for Bob Jim or Dave. Thus to get the row numbers

=IF(ROW(Sheet1!A2)-ROW(Sheet1!$A$2)+1>(COUNTIF(Sheet1!$A$2:$A$102,"Bob")+COU
NTIF(Sheet1!$A$2:$A$102,"Jim")+COUNTIF(Sheet1!$A$2:$A$102,"Dave")),"",SMALL(
IF(Sheet1!$A$2:$A$102={"Bob","Jim","Dave"},ROW(Sheet1!$A$2:$A$102),""),ROW(S
heet1!A2)-ROW(Sheet1!$A$2)+1))

I'll work on the exclusive and better formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"johnT" <anonymous@discussions.microsoft.com> wrote in message
news:009301c532e3$9cfabc10$a501280a@phx.gbl...
> Bob,
>
> Is it possible to search for all salesmen that are not
> Bob, Jim or Dave???
> (thanks)
>
> >-----Original Message-----
> >Just a couple of points, this formula
> >
> >=INDEX(Sheet1!B1:B100,A1)
> >
> >should be abso9lute for copying down
> >
> >=INDEX(Sheet1!$B$1:$B$100,A1)
> >
> >and you get extra data in the same sort of way
> >
> >=INDEX(Sheet1!$D$1:$D$100,A1)
> >
> >--
> >
> >HTH
> >
> >RP
> >(remove nothere from the email address if mailing direct)
> >
> >
> >"johnT" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:222101c532cb$be6f1cf0$a601280a@phx.gbl...
> >> yes, this works great...thanks again for all your help!!
> >> >-----Original Message-----
> >> >John,
> >> >
> >> >I think a bit more information would help, but here is
> a
> >> starter
> >> >
> >> >Assuming the salesmen are list in A1:A100 of sheet1
> >> >
> >> >on Sheet2, add this to A1, assuming a salseman of Bob
> >> >
> >> >=IF(ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1>COUNTIF(Sheet1!
> >> $A$1:$A$100,"Bob"),"",S
> >> >MALL(IF(Sheet1!$A$1:$A$100="Bob",ROW(Sheet1!
> >> $A$1:$A$100),""),ROW(Sheet1!A1)-
> >> >ROW(Sheet1!$A$1)+1))
> >> >
> >> >and copy down to A100 This is an array formula so
> commit
> >> with
> >> >Ctrl-Shift-Enter.
> >> >
> >> >You now have the row numbers of the matching salesmen
> on
> >> sheet1 with no
> >> >gaps. You then just get the data like so in B1
> >> >
> >> >=INDEX(Sheet1!B1:B100,A1)
> >> >
> >> >etc.
> >> >
> >> >--
> >> >
> >> >HTH
> >> >
> >> >RP
> >> >(remove nothere from the email address if mailing
> direct)
> >> >
> >> >
> >> >"johnT" <anonymous@discussions.microsoft.com> wrote in
> >> message
> >> >news:194301c53269$73231100$a401280a@phx.gbl...
> >> >> I have a rather large spreadsheet listing salesmen,
> >> >> customers, locations etc. I would like to create
> >> separate
> >> >> worksheets for each salesman listing only data
> related
> >> to
> >> >> that salesman...on each worksheet i want to referance
> >> the
> >> >> master worksheet, how can i do this without having a
> >> large
> >> >> group of blank rows??? I think this may be an
> >> application
> >> >> for array formulas but i need some help.
> >> >>
> >> >> thanks in advance....
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Regarding the original question and Bob's solution, here's a simpler
formula that is not an array formula.

I named the salesmen range "Salesmen", including the header cell if
there is one. On Sheet2, leave cell A1 blank, and enter the following
formula in cell A2. Then copy it down through A101:

=IF(COUNTIF(Salesmen,"Bob")<ROW()-1,"",MATCH("Bob",OFFSET(Salesmen,A1,0),0)+A1)

This will also get you the row numbers, then follow Bob's solution
starting in B2.

Gary

Hate to sound like a sourpuss, because I do agree a non-array is better than
an array if you can get it, but there is a fundamental aspect of mine that
yours doesn't cover (as presented).

If mine is adapted to a named range like yours, it looks better :-)

=IF(ROW(Sheet1!A2)-ROW(Salesmen)+1>(COUNTIF(Salesmen,"Bob")),"",SMALL(IF(Sal
esmen="Bob",ROW(Salesmen),""),ROW(Sheet1!A2)-ROW(Salesmen)+1))

The main point though is that yours works fine if the data starts in row 1.
Mine works even if the salesmen data starts in row 199. The printout
formula needs to still start the index at row 1, but that apart it is
resilient.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GaryDK" <megafour712345@comcast.net> wrote in message
news:1111948507.188665.141440@g14g2000cwa.googlegroups.com...
> Regarding the original question and Bob's solution, here's a simpler
> formula that is not an array formula.
>
> I named the salesmen range "Salesmen", including the header cell if
> there is one. On Sheet2, leave cell A1 blank, and enter the following
> formula in cell A2. Then copy it down through A101:
>
>
=IF(COUNTIF(Salesmen,"Bob")<ROW()-1,"",MATCH("Bob",OFFSET(Salesmen,A1,0),0)+
A1)
>
> This will also get you the row numbers, then follow Bob's solution
> starting in B2.
>
> Gary
>

Bob,

Nothing sour in that! :-) I have to agree, yours does look better! And
I agree that "general purpose" (Although I would opt to tackle this in
code as Harlan suggests.

The formula I posted isn't sensitive to where the data starts, at least
based on a couple of quick tests, but it is sensitive to where *it*
starts. That's probably what you meant. When I modify it to be more
general purpose (regarding its initial cell placement), it gets a bit
uglier. Here it is with a starting cell of J7:

=IF(COUNTIF(Salesmen,"Bob")<ROW()-ROW($J$7)+1,"",MATCH("Bob",OFFSET(Salesmen,OFFSET(J7,-1,0),0),0)+OFFSET(J7,-1,0))

Anyway, thanks for your help. It's appreciated.

Regards,

Gary
(less two thru five for direct)

Bob Phillips wrote:
> Hate to sound like a sourpuss, because I do agree a non-array is
better than
> an array if you can get it, but there is a fundamental aspect of mine
that
> yours doesn't cover (as presented).
>
> If mine is adapted to a named range like yours, it looks better :-)
>
>
=IF(ROW(Sheet1!A2)-ROW(Salesmen)+1>(COUNTIF(Salesmen,"Bob")),"",SMALL(IF(Sal
> esmen="Bob",ROW(Salesmen),""),ROW(Sheet1!A2)-ROW(Salesmen)+1))
>
> The main point though is that yours works fine if the data starts in
row 1.
> Mine works even if the salesmen data starts in row 199. The printout
> formula needs to still start the index at row 1, but that apart it is
> resilient.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)

Hi Gary,

"GaryDK" <megafour712345@comcast.net> wrote in message
news:1112071388.092292.71290@z14g2000cwz.googlegroups.com...

> The formula I posted isn't sensitive to where the data starts, at least
> based on a couple of quick tests, but it is sensitive to where *it*
> starts. That's probably what you meant. When I modify it to be more
> general purpose (regarding its initial cell placement), it gets a bit
> uglier. Here it is with a starting cell of J7:
>
>
=IF(COUNTIF(Salesmen,"Bob")<ROW()-ROW($J$7)+1,"",MATCH("Bob",OFFSET(Salesmen
,OFFSET(J7,-1,0),0),0)+OFFSET(J7,-1,0))

Looks more like mine noe :-)



=IF(ROW(Sheet1!A2)-ROW(Salesmen)+1>(COUNTIF(Salesmen,"Bob")),"",SMALL(IF(Sal
esmen="Bob",ROW(Salesmen),""),ROW(Sheet1!A2)-ROW(Salesmen)+1))