Need a little help.
Starting to try an expand my Excel repertoire....

Here is the Sitrep.

I have 2 worksheets...
Sheet 1
10 names of Owners
Ex:
Joe
Jim
Bob
etc...

Sheet 2
Row headers are as follows
name owner position salary

columns of name, position, salary will have data entered but i wish to
have a drop down box to select an owner from Sheet 1.

Then i would like to be able to have to sheets 3 to 12 (one for each
owner) to have the rows extracted for the entries that are equal to the
owners name.

Ex:
Sheet 3(Jim)
would only list name owner position salary of entries from sheet 2 that
have jim as the owner.

Hope that was clear enough as any help would be appreciated


--
jpsprack

Hi

No need for 13 pages at all.

Create a 3-page workbook with pages p.e. Data, Owner, Report


Owner: Header A1. Column Name
Define a named range (InsertNameDefine) Owner
=OFFSET(Owners!$A$1,1,,COUNTA(Owners!$A:$A)-1,1)

Data: Headers A1:E1. Columns RepRow, Name, Owner, Position, Salary
Define named ranges
LastRow=MATCH(MAX(Data!$A:$A),Data!$A:$A,0)
DataTbl=OFFSET(Data!$A$1,1,,LastRow-1,5)
Select some amount of rows in Owner column, and implement data validation
list (DataValidation, Allow=List) with Source set to
=Owner

Report:
A1=""Owner"
B1 - data validation list with Source set to
=Owner
Define a named range RepOwner
=Report!$B$1

On sheet Data, into A2 enter the formula
=IF(C2=RepOwner,COUNTIF(C$2:C2,RepOwner),"")
Copy the formula down for some amount of rows (same as you have data
validation in Owner column).
Hide the column RepRow

On sheet Report, into A3:D3 enter headers: Name, Owner, Position, Salary
A4=IF(ISERROR(VLOOKUP(ROW()-3,DataTbl,2,0)),"",VLOOKUP(ROW()-3,DataTbl,2,0))
B4=IF($A4="","",VLOOKUP(ROW()-3,DataTbl,3,0))
C4=IF($A4="","",VLOOKUP(ROW()-3,DataTbl,4,0))
D4=IF($A4="","",VLOOKUP(ROW()-3,DataTbl,5,0))

Copy A4:B4 down for as much rows as you think you'll have max rows for any
single owner.

It's done. Fill tables Owner and Data. Select any owner on Report, and all
rows for this owner from sheet Data are displayed.

PS. You can also do much simpler: Use autofilter (DataAutofilter) on sheet
Data. Set filter to specific owner - only rows for this owner are displayed.
(And you can drop RepRow column along with Report sheet and all named ranges
except Owner)


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"jpsprack" wrote in message
...

Need a little help.
Starting to try an expand my Excel repertoire....

Here is the Sitrep.

I have 2 worksheets...
Sheet 1
10 names of Owners
Ex:
Joe
Jim
Bob
etc...

Sheet 2
Row headers are as follows
name owner position salary

columns of name, position, salary will have data entered but i wish to
have a drop down box to select an owner from Sheet 1.

Then i would like to be able to have to sheets 3 to 12 (one for each
owner) to have the rows extracted for the entries that are equal to the
owners name.

Ex:
Sheet 3(Jim)
would only list name owner position salary of entries from sheet 2 that
have jim as the owner.

Hope that was clear enough as any help would be appreciated


--
jpsprack

Thanks! I will give that a shot.


--
jpsprack

Thanks Arvi it worked like a charm. Now for me to follow up with the
commands so i actually learn how it works :)


--
jpsprack