Complex macro - Any ideas would be great

[Gareth.Evans]

HI all,

I am strugling to produce the bellow functionallity for a reporting
system.

I have a main workbook ('Invoice') which contains invoice numbers, i
needs to look up data in several other workbooks
('January','Febuary','March'), consolidate the data corrosponding to a
certain invoice, then import it into a sheet in the main workbook.

EXAMPLE:

Workbook 'Invoice', sheet 1:

Invoice Number | Lookup Workbook
2 January
3 Febuary
4 March
5 Febuary
6 January


Workbook 'Febuary', sheet 1:

Invoice Number | Amount
3 10
3 30
3 15
5 50
5 30

FINAL Desired Result in Workbook 'Invoice', sheet 2

Invoice Number | Amount
3 55
5 80

Hope that makes sense. This is obvoicesly a simple example, in real i
need to import many other fields for thousands of records.

Realy hope someone can help me.

Kind Regards,

 

[smartin]

HI all,

I am strugling to produce the bellow functionallity for a reporting
system.

I have a main workbook ('Invoice') which contains invoice numbers, i
needs to look up data in several other workbooks
('January','Febuary','March'), consolidate the data corrosponding to a
certain invoice, then import it into a sheet in the main workbook.

EXAMPLE:

Workbook 'Invoice', sheet 1:

Invoice Number | Lookup Workbook
2 January
3 Febuary
4 March
5 Febuary
6 January


Workbook 'Febuary', sheet 1:

Invoice Number | Amount
3 10
3 30
3 15
5 50
5 30

FINAL Desired Result in Workbook 'Invoice', sheet 2

Invoice Number | Amount
3 55
5 80

Hope that makes sense. This is obvoicesly a simple example, in real i
need to import many other fields for thousands of records.

Realy hope someone can help me.

Kind Regards,

Have a look at Debra Dalgleish's explanation of the technique here:

http://www.contextures.com/xlFunctions05.html#RefWkbk

 

[Patrick Molloy]

several possible solutions come to mind - "there's more than one way to skin
a cat" - sorry cat people

however, it depends on how many records you have

also, in your Invoice sheet, will an invoice number only have the one month
as per your example?

my first suggestion would be to bring all the data from the January -
December worksheets into one sheet in the invoice workbook, and then you can
just use the SUMIF function.

the following code should be pasted into a code module

Option Explicit
Sub RefreshData()
Dim mnth As Long ' index for monthly workbooks
Dim sfile As String ' "root" file name - January, February etc
Dim sfilename As String ' full filename if found eg January.xls
Dim wb As Workbook ' object for the monthly workbook
Dim source As Range ' data from monthly book
Dim target As Range ' target range in Invoice book
'clear target table
Sheet2.Cells.ClearContents

For mnth = 1 To 12
sfile = Format(DateSerial(Year(Date), mnth, 1), "MMMM")
sfilename = Dir(sfile & ".xls")
If sfilename <> "" Then
Set wb = Workbooks.Open(sfilename)
Set source = wb.Worksheets("sheet1").Range("A1").CurrentRegion
With ThisWorkbook.Worksheets("sheet2")
If .Range("A1") = "" Then
Set target = .Range("A1")
Else
Set target = .Range("A1").End(xlDown).Offset(1)
End If
End With
With source
target.Resize(.Rows.Count, .Columns.Count).Value = .Value
target.Offset(, 2).Resize(.Rows.Count, 1) = sfile
End With

wb.Close False
Set wb = Nothing
End If
Next
End Sub

After running this sub, you'll have in sheet2 a table with three columns
A is the Invoice
B is the Amount
C is the Month

all you need to do now is an array formula in c,
eg C2
{==SUM((Sheet2!A2:A9=Sheet1!A2)*(Sheet2!C2:C9=Sheet1!B2)*(Sheet2!B2:B9)) }