Data Collect from another sheet!!

[alamgiritbd]

Hi,


Sheet1 DATA:

colA colB colC colD colE


Switch Site_Name_AXB BCFID BTSID ETID


CBN34 Mazu_Chudhurirhat 17 55

CBN33 Gondhorbapur 18 58 280

CBN31 Shakchar 19 61 281

CBN35 Shamsherabad 20 67 283

CBN35 Godown Road 21 70 284

CBN33 Mazupur 22 73 285,286 224

CBN33 Ataiatali 23 79 287

CBN34 Jakshinbazar 24 82 292

CBN31 Mandari 25 85 293 277

CBN31 Laxmipur 26 88 294,295

CBN31 Bancha_Nagar 27 94 296


I need when I choose switch number(CBN31) in sheet2 (A1) from a drop down list, then it will collect all data from sheet1 and show them like this:


Switch Site_Name_AXB BCFID BTSID ETID


CBN31 Shakchar 19 61 281

CBN31 Mandari 25 85 293 277

CBN31 Laxmipur 26 88 294,295

CBN31 Bancha_Nagar 27 94 296


Can you help me to solve this problem without using macro?


Thanks

ALAMGIR

 

[alamgiritbd]

For ex: please download the link

http://rapidshare.com/files/438493025/Link_Database_Mannaf__test_.xlsx

 

[Hui]

Alamgiritbd


Have a look at 2 examples of a report area and extract with formulas.

Refer these examples:


http://rapidshare.com/files/430489122/Sorter__Dates_.xls

or

http://rapidshare.com/files/427379371/Sorter.xls

 

[alamgiritbd]

Hui,


I all ready chk that file. but i need other thing.

can you please chk my upload file? you can find there what i want to do.

please see sheet3, i need all data from master sheet to sheet3 by clicking B1 cell.


http://rapidshare.com/files/438493025/Link_Database_Mannaf__test_.xlsx


Thanks

 

[Hui]

Alamgiritbd


I used the techniques presented in those 2 files with a small modification

Have a look at

I would change the ranges to Dynamic named ranges if you are going to be adding to the data source regularly


http://rapidshare.com/files/438535151/Link_Database_Mannaf__test_Hui.xlsx

 

[alamgiritbd]

Hui,


Yes i wants this.

Thanks


But when i changed CBN31 to CBN44/other in sheet3 cell B1 then it is show #N/A. it is unable to collect data for CBN44/other value.

can you explain why it is show like this? or how i solved this?


BTW, i am not expert on excel like you. so please help me to solve this problem.


Thanks

 

[Hui]

Whoops


copy Master!G5 to G4


so G4 should read =IF(B4=Sheet3!$B$1,ROW(),0)

 

[alamgiritbd]

Hui,


Thanks A Lot..........

It's working.


Thanks for your support.