Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 172
Default Pull data (sumif?) from an external workbook

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8,520
Default Pull data (sumif?) from an external workbook

Try the below macro...Edit the path and Sheet name for Sales.xls

Sub MyMacro()
Dim wb As Workbook, lngRow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks.Open("d:\Sales.xls")
Set ws2 = wb.Sheets("Sheet1")

For lngRow = 1 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Range("B" & lngRow) = Application.SumIf(ws2.Range("A:A"), _
ws1.Range("A" & lngRow), ws2.Range("b:b"))
Next

wb.Close False
Application.ScreenUpdating = True
End Sub


--
Jacob


"Simon" 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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Pull data (sumif?) from an external workbook


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
what is the best way to pull in external data from SQL table Lorina Excel Discussion (Misc queries) 4 November 13th 09 09:16 PM
Pull parameter from worksheet for External Data Query to SQL Datab Matt J Excel Discussion (Misc queries) 0 December 7th 06 11:20 PM
UDF alternative to PULL that opens external workbook and grabs val Shawn Excel Worksheet Functions 2 August 17th 06 10:12 PM
External Data to be used as in a pull down menu in Excel2003 keith perdue via OfficeKB.com Excel Discussion (Misc queries) 1 January 26th 05 03:39 PM
How do I pull multiple queries of external data from the Internet into Excel? JSD Excel Programming 1 September 22nd 03 11:24 PM


All times are GMT +1. The time now is 01:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"