Open workbook and perform sumproduct

[pitbulluk]

Hi

I have a workbook that contains two sheets that has 20k of rows of data and in a summry sheet I have a SUMPRODUCT and SUMIF formula together which performs the sum function on the large set of data based on a range of criteria on another sheet. Here's the formula:


=SUMPRODUCT(SUMIFS(BalSht!$F$2:$F$20175,BalSht!$B$2:$B$20175,CedantCode,BalSht!$K$2:$K$20175,'2. Booked Financials'!$A46))


the problem I have, is that by combining the large data set into my workbook it is becoming slow and unwieldy. I was thinking of having a macro button that when the user has input the criteria, they click the button and it then opens up another workbook and performs the sumproduct(sumif formula.

Im not sure of going about this with VBA

Your help would be appreciated.

[pitbulluk]

Can anyone point me in the right direction for the VBA to open the workbook and then perform a function?

Cheers

[pitbulluk]

Anyone?

Thanks

[alansidman]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[pitbulluk]

OK

The user inputs their codes on input form, These will change and there will be variable numbers of codes and must therefore be in the confines of teh range named "Codes".

The summary tab is the SUMPRODUCT results, however, I do not want to store the data in the same workbook as there are 20000 rows of data.

I was thinking that there could be a button for the user to click and it will open up the workbook with the data and perform the sumproduct function. I'm no expert, but you might have an alteranative way.

Appreciate your help.

[pitbulluk]

your help would be very much appreciated