Milo_Minderbinder
Well-known Member
- Joined
- Mar 2, 2006
- Messages
- 548
I am creating summary report that will pull data from several similar files (template).
All the files are in the same directory and the links will all be the same.
In the report file, I run a code that will list all the files in the directory in Col A. Then the code will insert formulas in various columns that will give me the values from certain cells for each of the listed files via links
eg
This way, I never have to open any of the source documents, and there could be hundreds.
This works fine, if not the best method, for individual cell values. I need a way to get more info from ranges like sum, average, count etc.
The summarysheet looks something like this:
Total, User and Date will always be in Cells A2, A3 & A4 of the source file.
I haven't had any luck with INDIRECT, either.
Any ideas?
All the files are in the same directory and the links will all be the same.
In the report file, I run a code that will list all the files in the directory in Col A. Then the code will insert formulas in various columns that will give me the values from certain cells for each of the listed files via links
eg
Code:
ActiveCell.Offset(0, 1).Formula = "='C:\MyDocs\Excel\[" & Filenm & "]Sheet1'!$A$2"
This way, I never have to open any of the source documents, and there could be hundreds.
This works fine, if not the best method, for individual cell values. I need a way to get more info from ranges like sum, average, count etc.
The summarysheet looks something like this:
Book1 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | FileName | Total | User | Date | Average | CountInputs | ||
2 | File1 | $100 | User1 | 10/2/2006 | ####### | ####### | ||
3 | File2 | $150 | User3 | 11/5/2005 | ####### | ####### | ||
4 | File3 | $200 | User15 | 11/15/1955 | ####### | ####### | ||
Sheet1 |
Total, User and Date will always be in Cells A2, A3 & A4 of the source file.
I haven't had any luck with INDIRECT, either.
Any ideas?