Help with INDIRECT and VLOOKUP

[Thresh1642]

Hi,

I am looking to do the following, but my excel-fu is weak....

I have twelve tabs, one for each month.
Each Tab contains a list of people, and the data pertaining to them, like the following

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Nuts[/TD]
[TD]Bolts[/TD]
[TD]Widgets[/TD]
[TD]Objects[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Ted[/TD]
[TD]11[/TD]
[TD]16[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]9[/TD]
[TD]17[/TD]
[TD]12[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]11[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

In the 13th tab, I am creating a graph to compare the people and associated data, and the associated data is in a table as well. The table would look like the following:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Month[/TD]
[TD]Nuts[/TD]
[TD]Bolts[/TD]
[TD]Widgets[/TD]
[TD]Objects[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]January[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]April[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]October[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]December[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


The problem I am running into is the formula needed to populate the cells with the '?' in them. I'm pretty sure its a combination of VLOOKUP (based on the Value in Name), and INDIRECT (based on the name of the table being referenced in Month), but I'm obviously missing something as my previous efforts have failed.

Any and all help appreciated!

Thanks,

Andrew

 

[AlanY]

the typical month tab

Book1
	A	B	C	D	E
1	Name	Nuts	Bolts	Widgets	Objects
2	Bob	12	14	6	6
3	Ted	11	16	5	5
4	Amy	9	17	12	9
5	Jeff	8	7	11	8
January

Book1
	A	B	C	D	E
1	Name	Nuts	Bolts	Widgets	Objects
2	Bob	24	28	12	12
3	Ted	22	32	10	10
4	Amy	18	34	24	18
5	Jeff	16	14	22	16
April

Book1
	A	B	C	D	E	F
1	Name	Month	Nuts	Bolts	Widgets	Objects
2	Bob	January	12	14	6	6
3	Bob	April	24	28	12	12
4	Amy	October	9	17	12	9
5	Jeff	December	16	14	22	16
Summary

Cell Formulas
Range	Formula
C2	=VLOOKUP($A2,INDIRECT($B2&"!$A:$E"),MATCH(C$1,INDIRECT($B2&"!$A$1:$E$1"),0),0)

 

[alpadem]

Hi Tresh1642,

I assume tab names are January, February,... etc.

In cell C2 you should write and drag right and down, you should change references if you need.

=INDEX(INDIRECT($B2&"!$A$1:$E$25");MATCH($A2;INDIRECT($B2&"!$A$1:$A$25");0);MATCH(C$1;INDIRECT($B2&"!$A$1:$E$1");0))

 

[Thresh1642]

Thanks for the replies!

Thresh