VBA Script required

[Lakshmi Narain]

Dear All,

Please help me with the VBA Script.

In the Attached file in sheet 1 there is a combo box. If we select Name 1. The blank data's should be filled with the reference of Sheet1!C6. But datas are taken from diffrent sheet. In Sheet1!D:D I have mentioned all sheet name as it is. I want to use the sheet Name as reference to take the data.

 

[Sathish KV]

Hi,

Use the below formula in cell E6. No changes when copying across but when coping down you need to change the sheet name

=SUMPRODUCT(('P1'!$A$2:$A$11=$C$6)*(Sheet1!$E$5:$P$5=E$5)*(('P1'!$B$2:$M$11)))

To make to work when combobox value changed fix Cell C6 as the linked cell in your combobox properties

 

[Jake Collins]

Dear Lakshmi

Or use this formula in E6 and copy down and across (without making changes).

=VLOOKUP($C$6,INDIRECT("'"&$D6&"'!$A$2:$M$11"),COLUMN(B6),FALSE)

 

[Lakshmi Narain]

Dear Lakshmi

Or use this formula in E6 and copy down and across (without making changes).

=VLOOKUP($C$6,INDIRECT("'"&$D6&"'!$A$2:$M$11"),COLUMN(B6),FALSE)

Thanks Jack Collins,
Can you help me with macros. Actually i have huge data if i use formula it will take few sec for calculation.
I Tried with If condition in VBA. But i am beginner for VBA. I dont know how to make another loop for another sheet.

 

[Jake Collins]

Well if you really need a macro then you would need a statement such as

For each Sheet in Sheets
If left(sheet.name,1)= "p" then

end if
next Sheet

 

[Lakshmi Narain]

Well if you really need a macro then you would need a statement such as

For each Sheet in Sheets
If left(sheet.name,1)= "p" then

end if
next Sheet

I tried How to run it. Can u help with with my attached data

 

[Lakshmi Narain]

Hello Lakshmi,

Say thanks to everyone who help you in you problem.

Regards,

Zameer

I am Thanking on first reply. Not in every reply. Thanks for reminding me i forget to reply sathish.

 

[Lakshmi Narain]

Hi,

Use the below formula in cell E6. No changes when copying across but when coping down you need to change the sheet name

=SUMPRODUCT(('P1'!$A$2:$A$11=$C$6)*(Sheet1!$E$5:$P$5=E$5)*(('P1'!$B$2:$M$11)))

To make to work when combobox value changed fix Cell C6 as the linked cell in your combobox properties

Dear Sathish,
Thanks for your reply,
Its working like V look up but not checking in another sheet. Can you help me with macro.

 

[Somendra Misra]

Hi Lakshmi,

Mean time somebody turn up with Macro solution here is a formula solution, note I cant comment on it's performance on a large data set. It's just an idea.

Regards,

 

[Somendra Misra]

See the file, for macro solution, I had attached the macro to the button on Sheet1.

Regards,