Sumproduct in excel using external data source

0
votes

I am trying to calculate some data in a excel using external excel file. My formula is like;

=SUMPRODUCT(--('[Stock Register.xlsx]Stock Code 1'!$E$3:$E$500="Issued to Sanitary Syndicate"),'[Stock Register.xlsx]Stock Code 1'!$C$3:$C$500)

This works fine for me. But I need the sheet (Stock Code 1) being changed automatically when dragging or pasting on the next rows.

I am using it a new workbook using external data source (Stock Register.xlsx). Stock Register consisting of 570 sheets and for each sheets I need to display results in that New Workbook in cell (C1) using above formula and drag it to cell (C70).

1
excelexcel-formula
To what will it be changed? You will need to incorporate INDIRECT some how, but it depends on how the name will change. - Scott Craner
You may have to use VBA for that! ;) But read the tour first! And edit your post to provide more details about which range you are trying to drag that formula on. - R3uK
Please help.... - arijitirf

1 Answers

0
votes

You can add two columns before the SUMPRODUCT.

First column, say this is in column B, will need to have CONCATENATE to the external spreadsheet that you want to link to. For example:

=CONCATENATE("[Stock Register.xlsx]Stock Code",ROW(A1),"!$E$3:$E$500")

This should give you [Stock Register.xlsx]Stock Code 1!$E$3:$E$500.

Same thing for the second column that you want for $C$3:$C$500 so you can get [Stock Register.xlsx]Stock Code 1!$C$3:$C$500.

Then the third column, say this is in column D, will be the SUMPRODUCT formula that with INDIRECT. And here you go:

=SUMPRODUCT(--(INDIRECT(B1)="Issued to Sanitary Syndicate"),INDIRECT(C1))

Hope this helps.

P.S. One thing I noticed when combining all three into one column, the formula wouldn't work. Someone probably can come up a better way but to have a reference address sit next to the result may benefit you to debug down the road. Just a thought.

UPDATED: Okay, I was able to recreate what I described below. Maybe it is easier to show you in screenshots:

  1. See I created three sheets in Stock Register.xlsx file, Stock Code 1 sheet looks like below that values under column C and descriptions under column E.

    And another spreadsheet called Results.xlsx. Column B has a formula of =CONCATENATE("'[Stock Register.xlsx]Stock Code ",ROW(A1),"'!$E$3:$E$500").

  1. In this picture, Stock Code 2 sheet looks like below.

    Column C in Results.xlsx spreadsheet has a formula of =CONCATENATE("'[Stock Register.xlsx]Stock Code ",ROW(A1),"'!$C$3:$C$500").

  1. In this picture, Stock Code 3 sheet looks like below.

    Column D in Results.xlsx spreadsheet has a formula of =SUMPRODUCT(--(INDIRECT(B1)="Issued to Sanitary Syndicate"),INDIRECT(C1)).

Try again and let me know.