Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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.... |
#2
|
|||
|
|||
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.... . |
#3
|
|||
|
|||
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.... |
#4
|
|||
|
|||
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.... |
#5
|
|||
|
|||
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 ---- |
#6
|
|||
|
|||
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.... |
#7
|
|||
|
|||
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.... . |
#8
|
|||
|
|||
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.... . |
#9
|
|||
|
|||
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.... . . |
#10
|
|||
|
|||
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.... . . |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
build up an array using formulas | Excel Discussion (Misc queries) | |||
Array formulas | Excel Worksheet Functions | |||
Problem with Array Formulas and ISNUMBER | Excel Worksheet Functions | |||
sumif and array formulas | Excel Worksheet Functions | |||
Using wild card characters in array formulas | Excel Worksheet Functions |