PDA

View Full Version : Fetching datas from different sheets onto one sheet



saleemin27
11-22-2014, 12:53 PM
hi I have 100 different named sheets, representing each sales man, each sheet having datas, where row titles are same, like Apple, Orange,Grapes. column heading are different ( like Jan'13 Feb'13 Mar'13 another sheet Mar'13 Apr'13 May 13)
I have a summary sheet also, in this A1 cell has a drop down list box showing row heading (Apple, Orange, Grape). when i select Orange in the drop down list box and when i select salesmen name (say 25names out of 100), it should show as follows: row heading would be the sheet name (sales man name), column heading would be the month. Can anyone help me with VBA codes or Excel formula.


Saleem (sheet name)



David (sheet name)







Jan'13
Feb'13
Mar'13


Mar'13
Apr'13
May'13




Apple
215
250
300

Apple
322
375
450




Orange
300
350
325

Orange
450
525
487




Grape
400
450
455

Grape
600
675
682




Pinapple
600
650
622

Pinapple
900
975
933






























Julee (sheet name)






Resulting sheet





Feb'13
Mar'13
Apr'13

Orange(list
box)
Salesmen
(2 out of 100 names)







Apple
1290
1500
1800


Jan'13

Feb'13
Mar'13
Apr'13
May'13


Orange
1800
2100
1950

Saleem
215
250
300
-
-


Grape
2400
2700
2730

David
-
-
322
375
450


Pinapple
3600
3900
3732

westconn1
11-22-2014, 06:46 PM
you can try a formula like
=VLOOKUP($A$1,INDIRECT(CONCATENATE($A2,"!a1:g99"),TRUE),MATCH(INDIRECT(ADDRESS(1,COLUMN()),TRUE),INDIRECT(CONCATENATE($A2,"!a1:g1"),TRUE),0),FALSE)

assumes orange or whatever are in cell A1 and names (equivalent to sheetnames) are in column A from row 2, change ranges for lookups to suit,