VB SCRIPTING IN EXCEL

[goodmans]

Hi Guys,

My source is Excel(.xls) file Worksheet1

COL1 COL2 COL3
US SALES 19
UK SALES 20
INDIA SALES 30
INDIA PUR 20
INDIA INV 50
CHINA SALES 15

IF I GIVE SEARCH PARAMETER AS "INDIA"
IT SHOUD POPULATE THE Worksheet2 WITH INDIA DETAILS

COL1 COL2 COL3
INDIA SALES 30
INDIA PUR 20
INDIA INV 50

Can some one tell me if this is possible with VB scripting in excel.

Regards
G

 

[goodmans]

I will pass the search parameter in the Main Worksheet of the same excel file. so it should get the parameter form Mainworksheet and do this work with in the excel file.

Regards
G

 

[lionelhill]

you can do this more conveniently with a pivot table. If you mark your first table and use the pivot table wizard, it can make a summary table, in which you drag "country" to the left side of the pivot table. This makes the pivot table a list of countries. Double clicking on any country will create a new worksheet containing all the rows of the first worksheet corresponding to the country you clicked on.

 

[goodmans]

Hi, actually my search parameter changes dynamically. I cant use the pivot table wizards etc. I have to use this excel functionality with other tool, so i wont have option of wizards or manual intervention.

So when data in search parameter cell changes. I want to dynamically populate the sheet3 with INDIA data.

Regards
G

 

[SkipVought]

search parameter changes dynamically.

i wont have option of wizards or manual intervention.

HUH?

So when data in search parameter cell changes

, that value can be automatically assigned to a PivotTable in order to display the data related to that selection.

Alternately, you could use a Parameter Query and achieve a similar result, with a bit more work.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]