Get data from another workbook

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Get data from another workbook

Post by adam »

I'm using the following formulas to get data from the sheets of the active workbook.

Code: Select all

=SUMIFS(tblOrderData!$G$5:$G$1048576, tblOrderData!$F$5:$F$1048576, $C11, tblOrderData!$A$5:$A$1048576, DATE($AH$9, MATCH($AA$9, {"January","February","March","April","May","June","July","August","September","October","November","December"}, 0), D$10))

Code: Select all

=COUNTIFS(tblMemoDetails!$N:$N,"Female",tblMemoDetails!$K:$K,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9),tblMemoDetails!$I:$I,$C98)

Code: Select all

=SUMIFS(tblOrderData!$G:$G, tblOrderData!$J:$J,"Male",tblOrderData!$A:$A,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9), tblOrderData!$I:$I,$C107)
How could I change the formula so that it would get values from the workbook "PMLIS2014" to the active work.

Any help on this would be kindly appreciated.

Thanks in advance.

Note: both the workbooks reside in the same folder E:\My Folder System
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get data from another workbook

Post by HansV »

Formulas with SUMIFS and COUNTIFS can refer to another workbook, but they will only work if that workbook is currently open in Excel. If the other workbook is closed, the formulas with display #VALUE! if you recalculate them. As soon as the other workbook is opened, the formulas will display the correct result again.

The formula

=COUNTIFS(tblMemoDetails!$N:$N,"Female",tblMemoDetails!$K:$K,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9),tblMemoDetails!$I:$I,$C98)

would have to be changed to

=COUNTIFS([PMLIS2014.xlsx]tblMemoDetails!$N:$N,"Female",[PMLIS2014.xlsx]tblMemoDetails!$K:$K,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9),[PMLIS2014.xlsx]tblMemoDetails!$I:$I,$C98)

if the full name is PMLIS2014.xlsx. If PMLIS2014 is a .xlsm workbook, change [PMLIS2014.xlsx] to [PMLIS2014.xlsm]

The changes to the other formulas are similar: prefix the sheet names with [PMLIS2014.xlsx]
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get data from another workbook

Post by adam »

I have the formula;

=SUMIFS(tblOrderData!$G$5:$G$1048576, tblOrderData!$F$5:$F$1048576, $C11, tblOrderData!$A$5:$A$1048576, DATE($AH$9, MATCH($AA$9, {"January","February","March","April","May","June","July","August","September","October","November","December"}, 0), D$10))

placed in cells D11:AH90.

The formula;
=COUNTIFS(tblMemoDetails!$N:$N,"Female",tblMemoDetails!$K:$K,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9),tblMemoDetails!$I:$I,$C98)

placed in cells D98:AH98.

The formula;

=SUMIFS(tblOrderData!$G:$G, tblOrderData!$J:$J,"Male",tblOrderData!$A:$A,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9), tblOrderData!$I:$I,$C107)

placed in cells D104:AH104.

Since using a lot of formula causes slow calculation is there a possibility that the formulas could be replaced by a worksheet event code or a standard module code? If so, how may I do it?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get data from another workbook

Post by HansV »

Assuming that you don't have hundreds of thousands of rows, it would be better to limit the number of rows in the formulas: instead of $G$5:$G$1048576 or $G:$G, use $G$5:$G$100 if your data won't go below row 100, or $G$5:$G$500 if they won't go below row 500, etc.

Depending on what you want to do, a pivottable might be better than using a lot of formulas.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get data from another workbook

Post by adam »

Thanks very much for the recommendation. Meanwhile;

I’m trying to exclude the column G from the following formula as I have no intention to include it in the formula anymore.

=SUMIFS([PMLIS2014.xlsm]tblOrderDetails!$G$5:$G$1048576, [PMLIS2014.xlsm]tblOrderDetails!$F$5:$F$1048576, $C13, [PMLIS2014.xlsm]tblOrderDetails!$A$5:$A$1048576, DATE($AH$9, MATCH($AA$9, {"January","February","March","April","May","June","July","August","September","October","November","December"}, 0), AA$10))

Column G contains the product quantity, whereby column A contains date, column F contains product name. How may I do it?

In the same way, I’m trying to exclude the column G from the following formula as well since I have no intention to include it in the formula anymore.

=SUMIFS([PMLIS2014.xlsm]tblOrderDetails!$G:$G, [PMLIS2014.xlsm]tblOrderDetails!$J:$J,"Female",[PMLIS2014.xlsm]tblOrderDetails!$A:$A,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9), [PMLIS2014.xlsm]tblOrderDetails!$I:$I,$C107)

Column G contains the product quantity, whereby column A contains date, column I contains product location and column J contains gender. How may I do it?

Any help on this would be kindly appreciated.

Thanks in advance.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get data from another workbook

Post by HansV »

If you exclude column G, the formula becomes totally meaningless, so you can simply remove the formula.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get data from another workbook

Post by adam »

The following formula sum ups the product with it's quantity present in the row.

For example;

if Row 1 with date 22/4/2014 has Apple in column F with a quantity of 3 in column G, the formula will sum up the total apple for the particular date as 3.

Instead, if the column G is excluded from the formula, cannot it calculate the total apple as 1; meaning count the rows with the particular product name within a specific date without having to include its quantity?

For example =SUMIFS([PMLIS2014.xlsm]tblOrderDetails!$G$5:$G$1048576, [PMLIS2014.xlsm]tblOrderDetails!$F$5:$F$1048576, $C13, [PMLIS2014.xlsm]tblOrderDetails!$A$5:$A$1048576, DATE($AH$9, MATCH($AA$9, {"January","February","March","April","May","June","July","August","September","October","November","December"}, 0), AA$10))

I hope I've made my question clear. Any help on this would be kindly appreciated.

Thanks in advance.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get data from another workbook

Post by HansV »

Do you mean this?

=COUNTIFS([PMLIS2014.xlsm]tblOrderDetails!$F$5:$F$1048576, $C13, [PMLIS2014.xlsm]tblOrderDetails!$A$5:$A$1048576, DATE($AH$9, MATCH($AA$9, {"January","February","March","April","May","June","July","August","September","October","November","December"}, 0), AA$10))
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Get data from another workbook

Post by adam »

Yes. Thanks for the help.

In the same manner I'm trying to eliminate column G from the following formula also,

=SUMIFS([PMLIS2014.xlsm]tblOrderDetails!$G:$G, [PMLIS2014.xlsm]tblOrderDetails!$J:$J,"Female",[PMLIS2014.xlsm]tblOrderDetails!$A:$A,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9), [PMLIS2014.xlsm]tblOrderDetails!$I:$I,$C107)

But my version as follows does not seem to be working;

=SUMIFS([PMLIS2014.xlsm]tblOrderDetails!$J:$J,
"Male",[PMLIS2014.xlsm]tblOrderDetails!$A:$A,DATEVALUE(D$10&"-"&$AA$9&"-"&$AH$9), [PMLIS2014.xlsm]tblOrderDetails!$I:$I,$C107)

What am I doing wrong in here?

Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Get data from another workbook

Post by Rudi »

The part that you eliminated ([PMLIS2014.xlsm]tblOrderDetails!$G:$G) is the sum_range in the expression. It is a compulsory argument because it instructs Excel which column to actually sum up if all the conditional are met.

If you are trying to simulate Hans's formula above you will see that he used a COUNTIFS() function which does not have a sum_range argument.

Syntax:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.