extract values from a workbook using sheet names criteria and multiple sheet data criteria

I have 11 /sheets in w/book out of which 10 sheets are sales personnel names i.e sheet1 named John,sheet2 William etc.Data of each sales man contains common such as products,month,region,sales etc.in different columns.My 11 th sheet I would like to name it as "Report" .How to extractdata from different sheets to destination sheet(Report) using sheet name criteria as well as individual sheets columns criteria.I am unable crete datavalidation list of all 10 sheets(excluding 11 th sheet).What formulae will get output.

Cell A1 = some sheet name like John

You want to return the value from cell X1 from the sheet name in cell A1.

=INDIRECT(A1&"!X1")

This evaluates to:

=John!$X$1

I don't see an attachment.

Try this in B5, copied across...
=INDEX(INDIRECT($B$1&"!A2:E15"),MATCH($C$1&$E$1&$G$1,INDEX(INDIRECT($B$1&"!A2:A15")&INDIRECT($B$1&"!C2:C15")&INDIRECT($B$1&"!D2:D15"),0),0),MATCH(B$3,INDIRECT($B$1&"!A1:E1"),0))

hmm this could be simplified if you are willing to use a helper column in each name sheet...
I used F, you can use whatever you want, and it can be hidden if needed...
F2=A2&C2&D2
copied down
Put this on every name sheet
=INDEX(INDIRECT($B$1&"!A2:E15"),MATCH($C$1&$E$1&$G$1,INDIRECT($B$1&"!F2:F15"),0),MATCH(B$3,INDIRECT($B$1&"!A1:E1"),0))

And thinking again, this whole process could be made far simpler if you had all data on 1 sheet, instead of multiple sheets...

data sheet...

	A	B	C	D	E	F	G
1	Product Name	Code	month	Region	sale	Name
2	Airconditioner	vcd	Jan	west	255$	John	JohnAirconditionerJanwest
3	Fan	cde	Mar	East	345$	John	JohnFanMarEast
4	Airconditioner	23e	Jan	west	765$	John	JohnAirconditionerJanwest
5	washing Machine	dse	Mar	north	350$	John	Johnwashing MachineMarnorth
6	cooler	bgf	Feb	south	750$	John	JohncoolerFebsouth
7	Fan	vfd	Feb	south	255$	John	JohnFanFebsouth
8	washing Machine	cds	Jan	north	345$	John	Johnwashing MachineJannorth
9	Airconditioner	dsa	Jan	East	765$	John	JohnAirconditionerJanEast
10	cooler	vfd	Feb	north	350$	John	JohncoolerFebnorth
11	cooler	bgf	Feb	West	800$	John	JohncoolerFebWest
12	Fan	nhg	Jan	south	255$	John	JohnFanJansouth
13	Fan	nbh	Jan	north	345$	John	JohnFanJannorth
14	Airconditioner	vfd	Feb	East	765$	John	JohnAirconditionerFebEast
15	washing Machine	vfd	Feb	north	350$	John	Johnwashing MachineFebnorth
16	washing Machine	vfd	Jan	west	255$	William	Williamwashing MachineJanwest
17	Airconditioner	cds	Mar	west	345$	William	WilliamAirconditionerMarwest
18	cooler	dsa	Feb	east	765$	William	WilliamcoolerFebeast
19	cooler	vfd	April	north	350$	William	WilliamcoolerAprilnorth
20	Fan	bgf	Jan	west	750$	William	WilliamFanJanwest
21	Fan	nhg	Jan	west	255$	William	WilliamFanJanwest
22	Airconditioner	nbh	Mar	east	345$	William	WilliamAirconditionerMareast
23	washing Machine	vfd	Feb	north	765$	William	Williamwashing MachineFebnorth
24	washing Machine	vfd	Mar	east	350$	William	Williamwashing MachineMareast
25	Airconditioner	vfd	Feb	north	800$	William	WilliamAirconditionerFebnorth
26	cooler	cds	April	west	255$	William	WilliamcoolerAprilwest
27	cooler	dsa	Jan	west	345$	William	WilliamcoolerJanwest
28	Fan	vfd	Jan	east	765$	William	WilliamFanJaneast
29	Fan	bgf	Mar	north	350$	William	WilliamFanMarnorth
30	Airconditioner	nhg	Feb	east	345$	William	WilliamAirconditionerFebeast
31	washing Machine	nbh	April	north	765$	William	Williamwashing MachineAprilnorth

G2=F2&A2&C2&D2

The extract formula then becomes...
B5=INDEX(data!A:A,MATCH(Report!$B$1&Report!$C$1&Report!$E$1&Report!$G$1,data!$G:$G,0))
copied down