Auto Populate Column

[Neilwvr]

Hi,

I have searched and found code but none seem to work...
Let's assume the following from a dummy table: Sheet = 'Users'

	A	B	C
1	Name	Empl ID	Manager
2	Mark	00001	Manager 1
3	Sue	00002	Manager 2
4	Joe	00003	Manager 3
5	Mary	00004	Manager 2
6	Jeffrey	00005	Manager 1
7	Eddy	00006	Manager 2
8	Henry	00007	Manager 1

<tbody>
</tbody>

Goal(s):
1) Get User/Employee details using Empl ID as the Lookup.
I have a sheet 'Manager Lookup' that get's details based on "Empl ID"(B1) below, from the above table 'Users' using various Vlookups (to get- Name, Empl ID, Manager,...etc)

Sheet = 'Manager Lookup'

	A	B
1	Empl#:	00002
2	Username:	Sue
3	Manager:	Manager 2
4

<tbody>
</tbody>

2) Using the data pulled via Vlookup to get the Manager in (B3) for the Username in (B2) as a New Lookup Field...
Populate a Column of ALL the Employees that fall under that Manager i.e. "Manager 2"

	A	B
5		Employees
6		Sue
7		Mary
8		Eddy
9

<tbody>
</tbody>

I would like this without using VBA.
Thanks.

 

[Fishboy]

Hi,

I have searched and found code but none seem to work...
Let's assume the following from a dummy table: Sheet = 'Users'

	A	B	C
1	Name	Empl ID	Manager
2	Mark	00001	Manager 1
3	Sue	00002	Manager 2
4	Joe	00003	Manager 3
5	Mary	00004	Manager 2
6	Jeffrey	00005	Manager 1
7	Eddy	00006	Manager 2
8	Henry	00007	Manager 1

<tbody>
</tbody>

Goal(s):
1) Get User/Employee details using Empl ID as the Lookup.
I have a sheet 'Manager Lookup' that get's details based on "Empl ID"(B1) below, from the above table 'Users' using various Vlookups (to get- Name, Empl ID, Manager,...etc)

Sheet = 'Manager Lookup'

	A	B
1	Empl#:	00002
2	Username:	Sue
3	Manager:	Manager 2
4

<tbody>
</tbody>

2) Using the data pulled via Vlookup to get the Manager in (B3) for the Username in (B2) as a New Lookup Field...
Populate a Column of ALL the Employees that fall under that Manager i.e. "Manager 2"

	A	B
5		Employees
6		Sue
7		Mary
8		Eddy
9

<tbody>
</tbody>

I would like this without using VBA.
Thanks.

Hi Neilwvr,

Goal 1 can be achieved with the following 2 Index / Match formulas:

B2 formula - =INDEX(Users!$A$2:$A$8,MATCH('Manager Lookup'!B1,Users!$B$2:$B$8,0))
B3 formula - =INDEX(Users!$C$2:$C$8,MATCH('Manager Lookup'!B1,Users!$B$2:$B$8,0))

I am currently not sure about goal 2 without using VBA, but I suspect there will be a way with just formulas.

 

[MARK858]

I don't see where

for the Username in (B2) as a New Lookup Field

comes into Goal 2 as you only need the Manger name

You can also achieve it with an Index Match. Please note: input the formula without the curly brackets, then commit the formula with Ctrl-Shift + Enter not just Enter. If done correctly Excel will insert the curly brackets.
Do not put in the curly brackets manually...,it won't work.

Formula in B6 and drag down.

Excel Workbook
	A	B
1	Empl#:	2
2	Username:	Sue
3	Manager:	Manager 2
4
5		Employees
6		Sue
7		Mary
8		Eddy
9
Manager Lookup

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Neilwvr]

Thanks for your response,

Goal 1 is already achieved as stated...

What I am trying to achieve with Goal 2 is similar to Extract a List of Values Filtered by Criteria with Sub-Arrays in Excel
I just can't seem to get the formula right for scenario.

 

[MARK858]

Please explain what is wrong with the results in post #3

 

[Neilwvr]

"to get the Manager in (B3) that was extracted for the Username in (B2)" - This was misinterpreted... I can see how misleading this was (My Bad)

This worked Perfectly! Thank you so much... I will have a proper look to see where I went wrong with mine.
Years back, it would have populated the entire column without the need to drag... I forgot that they changed that.

Thanks again.

 

[Neilwvr]

I did not see this till after I responded. You obviously commented while I was responding to the first response.

 

[MARK858]

it would have populated the entire column without the need to drag... I forgot that they changed that.

Not how I remember autofill working (copy/paste is a bit different), if you double click the bottom right corner it autofills down level with the data in the next column (if there is any) but that was always the same as far as my memory goes (probably only as far back as 2003 these days as a long long time since I used 95,97 or 2000 ).

and being honest you seriously don't want to fill an array formula down the 1 million+ rows there are these days unless you want to take a holiday while it calculates.

I did not see this till after I responded. You obviously commented while I was responding to the first response.

No problem