Instead of SUMIF you can use SUMPRODUCT, which works on closed
workbooks. With regards to the varying numbers of rows, you can use
dynamic range names that grow and shrink with the data.
To define a dynamic range name for all the numeric values in column A,
use in your Sales file
MyRange =$A$1:index($A:$A,match(99^99,$A:$A,1))
Create a similar range for your numbers
MyNumberRange =$B$1:index($B:$B,match(99^99,$A:$A,1))
Note: Anchor the Match function on the same column, so the ranges are
the same size.
Then reference these ranges in a SUMPRODUCT formula in your Report
workbook
=Sumproduct(--([Sales.xls]'Sheet1'!MyRange=A1),[Sales.xls]'Sheet1'!MyNumberRange)
regards, teylyn
Simon;674892 Wrote:
I have a workbook "report". Col A has contains products. The number of
products in column A may vary but each product features only once.
eg:
TOTAL SALES FOR JANUARY
ColA ColB
ItemX 4
ItemY 5
ItemZ 4
Another workbook "sales" contains the individual sales of these items
in one
month. So Col A contains the products which may feature once or
several
times. Col B contains the Qty.
eg:
SALES FOR JANUARY
ColA ColB
ItemX 1
ItemX 3
ItemY 5
ItemZ 2
ItemZ 2
Both workbooks reside in the same folder.
I want to write vba code which will populate the sum of sales for each
product for the month into Col B of "report".
I think a SumIf might be the way to go except for the following
issues:
"Sales" is an external workbook which is not open.
The rows in each workbook can vary depending on the number of products
or
the number of sales. "Sales" may contain 5, 10 rows of data or even
100.
"Report" may contain 5, 10 individual products or even 88.
Can anyone suggest code which will pull data from the closed workbook
"Sales" and populate ColB in "Report"
Hope this makes sense. In case you cant tell I'm a beginner.
Cheers
Simon
--
teylyn
Teylyn -- 'teylyn.posterous.com' (
http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=188544
http://www.thecodecage.com/forumz/chat.php