Has anyone established how to aggregate data located in separate excel files or separate invoices? We have a template and each invoice is saved as a separate excel file and invoice number auto increments after saving and exiting.
Wish to create a Monthly and Annual Report by extracting data from each invoice line item from multiple separate invoices and aggregating them into classifications by item id's.
Each line item in an invoice must use one of 4 different item ID's to ensure consistency. Must select either Equipment, Parts, etc. Where it gets tricky is one invoice might have several different items ID's (Equipment, parts, steel, etc. and in different line items on any given invoice, no consistency. Would need to search each line item, grab data and put it into a summary for each item ID.
I know this would be much easier using Access D/B and a query but for now would like to try to stay within excel.
Any suggestions, macros, would be appreciated. If anyone has or is doing this already would appreciate you getting in touch.
Sample Report which would be aggregated from maybe 20 different invoices in a month.
Monthly Report
Equipment Parts Steel General
2,592,525 1,525,252 489,352 125,478
Customer Report by Date
Date Customer Invoice # Sub-Total Total
Jun 1 ABC Company 210 1,000 1,245
Jun 2 ABC Company 215 5,000 5,125
thanks...hoping someone else has had to solve this one Mark
Wish to create a Monthly and Annual Report by extracting data from each invoice line item from multiple separate invoices and aggregating them into classifications by item id's.
Each line item in an invoice must use one of 4 different item ID's to ensure consistency. Must select either Equipment, Parts, etc. Where it gets tricky is one invoice might have several different items ID's (Equipment, parts, steel, etc. and in different line items on any given invoice, no consistency. Would need to search each line item, grab data and put it into a summary for each item ID.
I know this would be much easier using Access D/B and a query but for now would like to try to stay within excel.
Any suggestions, macros, would be appreciated. If anyone has or is doing this already would appreciate you getting in touch.
Sample Report which would be aggregated from maybe 20 different invoices in a month.
Monthly Report
Equipment Parts Steel General
2,592,525 1,525,252 489,352 125,478
Customer Report by Date
Date Customer Invoice # Sub-Total Total
Jun 1 ABC Company 210 1,000 1,245
Jun 2 ABC Company 215 5,000 5,125
thanks...hoping someone else has had to solve this one Mark
