Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allocate Files to Sheets and Build a Master Sheet which Summarises All Sheets
I am using Excell 97 and need to consolidate
many tables which are located in one folder as individual files onto one workbook as sheets. The Data is something like this: File 1: (Frank.xls) \\\\\File2: (Mary.xls)\\\\\\ File 3: (John.xls) \\\\\\\\1\\\\\\\\\\\2\\\\\\\\\\1\\\\\\\\\\\2\\\\\\ \\\\1\\\\\\\\\\\\\2 A (Software)\ Frank\\\ (Software)\\\ Mary \\\(Software)\\ John B Abode \\\\\\\\1 \\\\\\\Abode \\\\\\\1 \\\\\\\Abode\\\\\\\\\ 2 C A&T \\\\\\\\\\\3\\\\\\\ Photoshop\\ 1\\\\\\\\ A&T \\\\\\\\\\\\ 1 D MS Excel\\\\\ 1 \\\\\\\Win Zip \\\\\\2 \\\\\\\MS Excel\\\\\\\ 2 E MS Office\\\\\ 4 \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\Win Zip\\\\\\\\\ 1 F MS Word\\\\\ 1 G Win Zip\\\\\\\ 2 I'm trying to consolidate a report in Excel that calls the files and places them on a seperate sheet and then finds, sorts the software and creates a field with the persons name and the number associated with the software. In other words: I need to retrieve each file and place it on a new sheet for each file and then build a report to show which person has what software. The Macro needs to search, sort and fill itself with an assigned button. So if for example, there is no data on the 1st sheet for photoshop, I need to create a field when it appears on the 2nd sheet and so on. So the Result sheet will contain all sheets information on the one with out duplicates. If I change or add data to one of the files and run the macro it will update the appropriate sheet and give the correct result. Result: \\\\\\\\\\1\\\\\\\\\\ 2\\\\\\\\\\ 3\\\\\\\\\\ 4 A (Software)\\\\ Frank\\\\\Mary\\\\\ John B Abode\\\\\\\\\\\ 1\\\\\\\\\\ 1\\\\\\\\\\ 2 C A&T\\\\\\\\\\\\\\ 3\\\\\\\\\\\\\\\\\\\\\\\ 1 D MS Excel\\\\\\\\ 1\\\\\\\\\\\\\\\\\\\\\\\ 2 E MS Office\\\\\\\ 4 F MS Word \\\\\\\\1 G Photo shop\\\\\\\\\\\\\\\\\ 1 H Win Zip\\\\\\\\\ 2\\\\\\\\\\\ 2\\\\\\\\\ 1 I hope that is enough information to help me with this problem. It is way over my head and it is taking me ages to do, as I have over 50 Files I need to sift through. I hope someone out there has done this before and can help me. Thanks in advance Hanuman. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste all sheets into one master sheet | Excel Worksheet Functions | |||
Other sheets to pick data from master sheet | Excel Discussion (Misc queries) | |||
Moving data from master sheet to new sheets | Excel Worksheet Functions | |||
Getting info from individual sheets into master sheet | Excel Worksheet Functions | |||
Getting info from individual sheets into master sheet | Excel Discussion (Misc queries) |