Report Generator

Something like that can be done, even down to the one "Generate" button, but depending on the content of the 100 reports and what you're looking for (or should be looking for, or should watch out for), it will require a lot of careful thought and design.

Suppose you leave the wrong thing out of the report, and some part of the business starts losing money, sales, has an explosion, ...?

Do all the reports contain the same information? (I mean, like you have 100 different locations all reporting the same information, but specific to their locations?) That could be something easier to do.

You would have to describe a lot more about what you're trying to do to get useful advice, and you may need to get some consulting help (from either in or out of house).

If your using excel for your spread sheets now, do you have access to Microsoft's "Access" program? This is a rational data base tool and can be inlaid into or over Excel. May take someone with "Access" knowledge in order to program file transfers correctly, but not to bad to learn if you had to. There are a lot of classes out there on "Access" as well. Maybe something someone there might consider doing to help your-selfs out with cost of programing.

Good Luck

If you type "Excel Formulas" into the Search all of CR4 (upper right corner of home screen), you will get a list of all discussions pertaining to that subject on CR4.

The very first has a link: http://www.mrexcel.com/ that seems to be helpful, but there are others.

wath is the origin of reports?

i.e.
-applications with databasses (CRM, ERP, industrial proccesses)
-people in your organisation (sales, personal, financial)
-Sales forces arround the country or similar
-recopilation of data from the web (markets, prices, tendences)

Suposed you use Outlook and receive suchs reports by email, you can automate the treatment of files using macros to save and rename the files received in a folder and use access -as it has been said- or excel to import significant data and manipulate it to obtain the desired one. Think about the mechanical (automatic) work you do to translate it to a process just as you where teaching other people wath to do and how (programing).

there are a lot of report tools in the market that can help you but needing a little IT knowledge and help, many are open source like JasperReports o Jxls (this is excel oriented).

hope this helps

As others have pointed out a database tool is better at organizing data, data can be sorted, specific reports can be designed and generated. A much better tool than a spreadsheet. The data can be imported and exported to a spreadsheet.

Spreadsheets are for complex calculations.

Get some training!

"Spreadsheets are for complex calculations."

I have to disagree: Excel is not just for complex calculations.

I actually find the more complex the calculation, the more likely that something will be wrong with is and excel will tell you there is a problem, but not how to fix it.

Whilst database programs (such as access) are great for organising data, creating charts, etc is easier done with excel. As part of my job, I access reports in access and copy the information into an excel document, so I can turn the information to something meaningful and useful!

I think, as others have said, it fully depends on the type of information and if the information is consistent.

If it is consistent (and you don't have access to a database program), a macro would most likely achieve the result your after, but it could be tricky if you don't know how to use them. Of course, there is a Macros for Dummies type book out there if you wanted to learn.

The other option is to use formulas that reference cells in other excel documents. But you would have to ensure that the file locations and names stayed the same, i.e. you wouldn't be able to change the location of the referenced documents without modfying the corresponding formula.

yes it is possible using an excel spread sheet, I have something similar however it was configured for me by an IT specialist, therefore if you are unable to configure this yourself then please ask an IT specialist that is what they are therefore.

Another piece of advice to make life simpler for yourself, all the reports should preferably be in the same format. I made up a standard template and requested the report writers to use my template.

Hope this helps.

Lots of good suggestions, but no response from the OP. More information is required to provide specific advice. Coo-eee therealabdo ...

thank you guys..I think i will choose either doing it on ACCESS or learning some MACROS and doing it on Excel

But I thought there would be a tool I can download where i can link multiple cells from multiple excel files to generate a new file and do calculations

I believe how much we know about EXCEL...there is always MORE :D

The tool is called Excel. :)

You can cross-link any cell from one sheet to another cell in another sheet, even if it's in another workbook (file). Click destination cell, press = key, switch to source workbook and worksheet, click in the source cell. The destination cell should now contain the formula for the cross-link. (I just tried it and it still works.)

You can continue to click-click your way through 100 reports and all their sheets and cells, or you can copy and paste the formula from cell to cell in your destination worksheet, tweaking it as needed to pull data from different cells/worksheets.

The formula looks like: ='[SOURCE FILE.xlsx]Worksheet Name'!$E$13

It's an error-prone process and will be tedious with the volume of data you have. Most importantly, if the format of your source data changes, the links don't change to keep up. But if all those input reports are very stable, the initial set-up could pay off.

You can always hire a programmer. Large concerns with large budgets have entire departments who do this sort of thing, of course.

"The formula looks like: ='[SOURCE FILE.xlsx]Worksheet Name'!$E$13"

The file extension as above only works for the 2007 edition (possible 2010 as well), for 2003 edition of excel just use .xls

I know 2003 edition is now old, however, I also know that there are a lot of places that haven't yet upgraded (the company I work for is only just updating to the 2007 edition).

What about going back to the senders and have each one of them tailor the reports sent to you contain ONLY the information needed by you. They should do this for each recipient. Then you and all of the others that receive all of these daily reports won't be spending additional time extracting information pertinent to their individual department and responsibilities.

If the 100 different reports are all in xls format, you can generate your summary report by using the import /export tool built-in the excel program. Using the 100 different reports as a look-up table for your "new personalized" report. By creating a new spreadsheet, then picking or importing only needed pertinent information into your new "page" and performing whatever mathematical operations within this newly formed spreadsheet. You need to do it manually first to test and see if what you wanted is what you get in the personalized report. Once satisfied with the outcome, you can now create some "macros' to automate the process. Also, since you are making a relational report, to facilitate data imports/ exports process, it is important that copies of all 100+ report, including your new one resides are all saved in one xls book.

There is also a program commercially available called "Report Writer" that will automate all this process for you, in case you are not into a programming mode!