Vlookup Change (sheet) Range

[h55nick]

Hello,

Currently I have a list of names on sheet 1. (named: names)

assume each name has a sheet that is named after it. (for the most part true)

I need to vlookup data that exists in each sheet (it moves around). I also have more then 500 names that change constantly so manually selecting the range would require to much time.

example:

Name: Number of reports:
Alex =vlookup("Report #", Alex!A1:D50,2,FALSE)
Ben =vlookup("Report #", Ben!A1:D50,2,FALSE)
John =vlookup("Report #", John!A1:D50,2,FALSE)
.
.
.
Forever (almost)

Basicially I need the sheet name in 2 to be = range("A2").value

I have tried multiple different things.
It seems that excel evaluates the "functions" in 2 and then wont ref that range.

Is this possible?

All help is great!

Thanks,
h55nick

 

[Sal Paradise]

Excel Workbook
	A	B
1	Alfa	3
2	Bravo	10
3	Charlie	10
4	Delta	2
5	Echo	4
6	Foxtrot	2
7	Golf	6
8	Hotel	1
9	India	2
10	Juliett	6
11	Kilo	9
12	Lima	5
13	Mike	4
14	November	8
15	Oscar	7
16	Papa	7
17	Quebec	10
18	Romeo	5
19	Sierra	9
20	Tango	4
21	Uniform	2
22	Victor	9
23	Whiskey	4
24	X-ray	7
25	Yankee	7
26	Zulu	3
Sheet1

 

[h55nick]

Awesome!!

Thank you!