Creating a internal phone book for a company.
Sheet 1 has the following details:
Division
Department A
employee 1 123456
employee X 123457
employee 3 123458

Department B
employee 2 987654
employee Y 987653
employee Z 987652
etc.

Sheet 2 is an alphabetical listing of all employees, regardless of which
dept. they work in.
My question is how can I get excel to automatically add the section they
work in after the other details ie:
employee 1 123456, Department A
employee 2 987654, Department B
employee 3 123458, Department A
employee X 123457, Department A
etc.
--
tia

how can I get excel to automatically add the section they work in

I'm assuming that section is the same as department.

Try this:

Sheet2 A1 = employee Y

=LOOKUP(2,1/SEARCH("department",Sheet1!A1:INDEX(Sheet1!A1:A15, MATCH(A1,Sheet1!A1:A15,0))),Sheet1!A1:INDEX(Sheet1 !A1:A15,MATCH(A1,Sheet1!A1:A15,0)))

Result = Department B


--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
Creating a internal phone book for a company.
Sheet 1 has the following details:
Division
Department A
employee 1 123456
employee X 123457
employee 3 123458

Department B
employee 2 987654
employee Y 987653
employee Z 987652
etc.

Sheet 2 is an alphabetical listing of all employees, regardless of which
dept. they work in.
My question is how can I get excel to automatically add the section they
work in after the other details ie:
employee 1 123456, Department A
employee 2 987654, Department B
employee 3 123458, Department A
employee X 123457, Department A
etc.
--
tia

I oversimplified the issue a bit: Departments A & B are in reality Admin,
Finance, Sales, Support and so on.
After the last name/phone number entry for every department there is an
empty row. Can the function 'work out' where this empty row is and 'look up'
to find the row above the first phone number entry as this is where the dept.
name will be?

--
tia


"T. Valko" wrote:

how can I get excel to automatically add the section they work in

I'm assuming that section is the same as department.

Try this:

Sheet2 A1 = employee Y

=LOOKUP(2,1/SEARCH("department",Sheet1!A1:INDEX(Sheet1!A1:A15, MATCH(A1,Sheet1!A1:A15,0))),Sheet1!A1:INDEX(Sheet1 !A1:A15,MATCH(A1,Sheet1!A1:A15,0)))

Result = Department B


--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
Creating a internal phone book for a company.
Sheet 1 has the following details:
Division
Department A
employee 1 123456
employee X 123457
employee 3 123458

Department B
employee 2 987654
employee Y 987653
employee Z 987652
etc.

Sheet 2 is an alphabetical listing of all employees, regardless of which
dept. they work in.
My question is how can I get excel to automatically add the section they
work in after the other details ie:
employee 1 123456, Department A
employee 2 987654, Department B
employee 3 123458, Department A
employee X 123457, Department A
etc.
--
tia

Departments A & B are in reality Admin, Finance, Sales, Support

Yeah, I kind of figured that would be the case!

Ok, this formula requires that there be an empty cell/row above where your
data starts. Let's use this sample:

...........A...
1..............
2.....Sales
3.....Jones
4.....Smith
5............
6....Admin
7....Brown
8....Lee
9....Adams

C2 = lookup value = Lee

Array entered** :

=INDEX(A1:A9,MAX(IF(A1:INDEX(A1:A9,MATCH(C2,A1:A9, 0))="",ROW(A1:INDEX(A1:A9,MATCH(C2,A1:A9,0)))-ROW(A1)+1))+1)

result = Admin

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
I oversimplified the issue a bit: Departments A & B are in reality Admin,
Finance, Sales, Support and so on.
After the last name/phone number entry for every department there is an
empty row. Can the function 'work out' where this empty row is and 'look
up'
to find the row above the first phone number entry as this is where the
dept.
name will be?

--
tia


"T. Valko" wrote:

how can I get excel to automatically add the section they work in

I'm assuming that section is the same as department.

Try this:

Sheet2 A1 = employee Y

=LOOKUP(2,1/SEARCH("department",Sheet1!A1:INDEX(Sheet1!A1:A15, MATCH(A1,Sheet1!A1:A15,0))),Sheet1!A1:INDEX(Sheet1 !A1:A15,MATCH(A1,Sheet1!A1:A15,0)))

Result = Department B


--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
Creating a internal phone book for a company.
Sheet 1 has the following details:
Division
Department A
employee 1 123456
employee X 123457
employee 3 123458

Department B
employee 2 987654
employee Y 987653
employee Z 987652
etc.

Sheet 2 is an alphabetical listing of all employees, regardless of
which
dept. they work in.
My question is how can I get excel to automatically add the section
they
work in after the other details ie:
employee 1 123456, Department A
employee 2 987654, Department B
employee 3 123458, Department A
employee X 123457, Department A
etc.
--
tia

Absolutely brilliant - great stuff, thanks very much.
Gold star for you :)
--
tia


"T. Valko" wrote:

Departments A & B are in reality Admin, Finance, Sales, Support

Yeah, I kind of figured that would be the case!

Ok, this formula requires that there be an empty cell/row above where your
data starts. Let's use this sample:

...........A...
1..............
2.....Sales
3.....Jones
4.....Smith
5............
6....Admin
7....Brown
8....Lee
9....Adams

C2 = lookup value = Lee

Array entered** :

=INDEX(A1:A9,MAX(IF(A1:INDEX(A1:A9,MATCH(C2,A1:A9, 0))="",ROW(A1:INDEX(A1:A9,MATCH(C2,A1:A9,0)))-ROW(A1)+1))+1)

result = Admin

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
I oversimplified the issue a bit: Departments A & B are in reality Admin,
Finance, Sales, Support and so on.
After the last name/phone number entry for every department there is an
empty row. Can the function 'work out' where this empty row is and 'look
up'
to find the row above the first phone number entry as this is where the
dept.
name will be?

--
tia


"T. Valko" wrote:

how can I get excel to automatically add the section they work in

I'm assuming that section is the same as department.

Try this:

Sheet2 A1 = employee Y

=LOOKUP(2,1/SEARCH("department",Sheet1!A1:INDEX(Sheet1!A1:A15, MATCH(A1,Sheet1!A1:A15,0))),Sheet1!A1:INDEX(Sheet1 !A1:A15,MATCH(A1,Sheet1!A1:A15,0)))

Result = Department B


--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
Creating a internal phone book for a company.
Sheet 1 has the following details:
Division
Department A
employee 1 123456
employee X 123457
employee 3 123458

Department B
employee 2 987654
employee Y 987653
employee Z 987652
etc.

Sheet 2 is an alphabetical listing of all employees, regardless of
which
dept. they work in.
My question is how can I get excel to automatically add the section
they
work in after the other details ie:
employee 1 123456, Department A
employee 2 987654, Department B
employee 3 123458, Department A
employee X 123457, Department A
etc.
--
tia

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
Absolutely brilliant - great stuff, thanks very much.
Gold star for you :)
--
tia


"T. Valko" wrote:

Departments A & B are in reality Admin, Finance, Sales, Support

Yeah, I kind of figured that would be the case!

Ok, this formula requires that there be an empty cell/row above where
your
data starts. Let's use this sample:

...........A...
1..............
2.....Sales
3.....Jones
4.....Smith
5............
6....Admin
7....Brown
8....Lee
9....Adams

C2 = lookup value = Lee

Array entered** :

=INDEX(A1:A9,MAX(IF(A1:INDEX(A1:A9,MATCH(C2,A1:A9, 0))="",ROW(A1:INDEX(A1:A9,MATCH(C2,A1:A9,0)))-ROW(A1)+1))+1)

result = Admin

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
I oversimplified the issue a bit: Departments A & B are in reality
Admin,
Finance, Sales, Support and so on.
After the last name/phone number entry for every department there is an
empty row. Can the function 'work out' where this empty row is and
'look
up'
to find the row above the first phone number entry as this is where the
dept.
name will be?

--
tia


"T. Valko" wrote:

how can I get excel to automatically add the section they work in

I'm assuming that section is the same as department.

Try this:

Sheet2 A1 = employee Y

=LOOKUP(2,1/SEARCH("department",Sheet1!A1:INDEX(Sheet1!A1:A15, MATCH(A1,Sheet1!A1:A15,0))),Sheet1!A1:INDEX(Sheet1 !A1:A15,MATCH(A1,Sheet1!A1:A15,0)))

Result = Department B


--
Biff
Microsoft Excel MVP


"JockW" wrote in message
...
Creating a internal phone book for a company.
Sheet 1 has the following details:
Division
Department A
employee 1 123456
employee X 123457
employee 3 123458

Department B
employee 2 987654
employee Y 987653
employee Z 987652
etc.

Sheet 2 is an alphabetical listing of all employees, regardless of
which
dept. they work in.
My question is how can I get excel to automatically add the section
they
work in after the other details ie:
employee 1 123456, Department A
employee 2 987654, Department B
employee 3 123458, Department A
employee X 123457, Department A
etc.
--
tia