array formulas

[johnT]

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....

[Biff]

Hi!

You don't necessarily need array formulas but it all
depends on what your layout is and what type of data you
want to compile.

You may be able to use simple lookups or, a lot of people
might recommend a pivot table. In any event, you would
need to give more details for a more specific suggestion.

Biff

>-----Original Message-----
>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....
>.
>

[Max]

Perhaps this previous post might give you s
ome possibilities to explore further: http://tinyurl.com/5ejr2
(the key column in your case would be the salesmen names)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"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....

[Bob Phillips]

Hi Max,

I wish I had seen that before I re-invented the wheel :-)

Regards

Bob


"Max" <demechanik@yahoo.com> wrote in message
news:u4zAk0rMFHA.568@TK2MSFTNGP09.phx.gbl...
> Perhaps this previous post might give you s
> ome possibilities to explore further: http://tinyurl.com/5ejr2
> (the key column in your case would be the salesmen names)
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "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....
>
>

[Max]

> I wish I had seen that before I re-invented the wheel :-)
LOL! Thought you should be relaxing a little on a Sunday ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Bob Phillips]

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....

[johnT]

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....
>
>
>.
>

[Bob Phillips]

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....
> >
> >
> >.
> >

[johnT]

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....
>> >
>> >
>> >.
>> >
>
>
>.
>

[Bob Phillips]

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....
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

[Harlan Grove]

johnT wrote...
>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.

Many responses with formulas, but if you want to use the single master
worksheet for data entry, so each salesperson's data in their own
worksheet would effectively be static data, you'd be better off using a
macro to to copy records to each individual's worksheet. This could
also provide the useful additional functionality of adding new
worksheets for new salespersons appearing in the list and deleting
worksheets for salespersons no longer appearing in the list (possibly
prompting you to confirm deletion).

If the sales data table were named SalesTable with salesperson ID in
the first/leftmost column, then something like


Sub foo()
Dim i As Long, j As Long, k As Long, n As Long
Dim id As String, ta As String
Dim dr As Range, nr As Range, tr As Range
Dim xr As Object, ws As Worksheet

Set xr = CreateObject("Scripting.Dictionary")

Set dr = ThisWorkbook.Names("SalesTable").RefersToRange
k = dr.Columns.Count - 1
n = dr.Rows.Count - 1
ta = ActiveSheet.Range("A3", Cells(3, k)).Address(0, 0) 'H/C
Set nr = dr.Offset(1, 0).Resize(n, 1)
Set tr = dr.Offset(0, 1).Resize(1, k)
Set dr = dr.Offset(1, 1).Resize(n, k)

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> dr.Worksheet.Name Then
id = ws.Range("B1").Value 'H/C
xr.Add id, ws.Name
ws.Range("A4:IV65536").ClearContents 'H/C

If Application.WorksheetFunction.CountIf(nr, id) = 0 Then

If MsgBox( _
Prompt:="Salesperson '" & id & "' has a " & _
"worksheet but no entries in SalesTable." & _
Chr(13) & Chr(13) & "Delete the worksheet?", _
Buttons:=vbYesNo, _
Title:="No Data" _
) = vbYes Then
ws.Delete

End If

End If

End If

Next ws

For i = 1 To n

id = nr.Cells(i, 1).Value

If xr.Exists(id) Then
j = .Worksheets(xr.Item(id)).Range(ta).End(xlDown).Row
If j = Rows.Count Then j = 1 Else j = j - 2
ThisWorkbook.Worksheets( _
xr.Item(id)).Range(ta).Offset(j, 0).Value = _
dr.Rows(i).Value

Else
Set ws = ThisWorkbook.Worksheets.Add(After:= _
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
xr.Add id, ws.Name
ws.Range("A1").Value = _
dr.Offset(-1, -1).Resize(1, 1).Value 'H/C
ws.Range("B1").Value = id 'H/C
ws.Range(ta).Value = tr.Value
ThisWorkbook.Worksheets( _
xr.Item(id)).Range(ta).Offset(1, 0).Value = _
dr.Rows(i).Value

End If

Next i

Set xr = Nothing

End Sub