array formulas
 

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

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

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 <atyahoo<dotcom
----
"johnT" wrote in 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....

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)+1COUNTIF(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" wrote in 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....

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)+1COUNTIF(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" wrote in
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....


.

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" wrote in message
...
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)+1COUNTIF(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" wrote in
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....


.

Hi Max,

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

Regards

Bob


"Max" wrote in message
...
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 <atyahoo<dotcom
----
"johnT" wrote in 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....

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" wrote in
message
...
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)+1COUNTIF(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" wrote in
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....


.



.

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,"Bo b")+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(She et1!$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" wrote in message
...
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" wrote in
message
...
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)+1COUNTIF(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" wrote in
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....


.



.

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

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 <atyahoo<dotcom
----

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" wrote in message
oups.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

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.Co unt))
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

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" wrote in message
ups.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))