Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Where I work, staff have just started to use a basic table in excel (1 October) to record sales which has about 10 columns (name, client, renewed, discount, paid etc). I record the data (total sold etc) every hour and email it to the manager. Each staff has the their own file on the network which they use constantly for that day (eg. John 08-10.xlsx; John 09-10.xlsx etc) and have been told to save the file after they complete a row with client data.

I can see the file (in read only mode) to update the report but I am sure there must be a way of doing an autoupdate of their worksheets in real time. I can link worksheets and workbooks to my main workbook but manually. Does anyone have suggestions on have to do this on Excel? Or would Access allow me to make a report which shows the sales total for that hour without the staff closing the file or constantly clicking save every few minutes?

share|improve this question
    
this sound more like typical task for SQL or Access or any Database - you should provide a centralized Database which you interface with whatever interface you find suiteable (Excel, App, Web, Access). You'll gain more conrtrol over the data-input and skip merging the data, as it will be merged at input. –  Jook Oct 9 '12 at 13:38

2 Answers 2

I did some reports in Excel (with and without VBA) and in Access. Reports in Excel and Access have their own strong and weak sides. Without being more specific, I can't help you well.

If you go with the Access solution, you will have to use VBA and spend at least a few days setting things up. In that case you might also consider using VB6 and write stand-alone application - the code is almost exactly the same, but debugging is much better under VB6, than in VBA (I mean: debugging classes). And you might easily use version control system.

As for Excel:

If you rely on formulas, reports in Excel can reliably feed only on already opened workbooks. Open workbooks don't update their contents if other program try to write to it (add new lines to a table).

So for the update part, you need some sort of VBA automation, but it will be very small and simple.

Excel have formulas, and you may do miracles with them. Personally I find it easier and faster to work with formulas rather than with VB code. If you base your report on formulas you have the interactivity for free. To dynamically format charts, you must use VB(A).

Long reports based on formulas often take long time to calculate.

share|improve this answer

Please check the link. Hope it helps you in some way.

LINK
Conclusion from the linked article:

Excel makes it easy to generate custom output with very flexible formatting and annotations that you can add anywhere. The payoff with Microsoft Access is how databases simplify things over time. It may be overkill for one time analysis, but if the data and reports need to be maintained over time, spreadsheets often hit a wall. Most organizations have many "similar" spreadsheets that are tweaked slightly differently and rapidly become unmaintainable. A well designed Access database avoids that limitation. That said, both Access and Excel have their strengths and weaknesses.

A hybrid solution where data from an Access database is exported or copied to Excel often provides the best of both worlds. The data integrity of a database with its well defined and approved output in conjunction with Excel for ad hoc analysis lets you leverage the advantages of both. Using automated processes, the sharing of data can be very smooth.

share|improve this answer
    
When providing answers it is very important that you provide enough information that your answer is useful if the link breaks in the future. Please add at least a summary of the content you are linking to. –  Zoredache Oct 9 '12 at 2:14
    
Welcome to Super User! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. –  Peachy Oct 9 '12 at 6:52
    
Oops.. I'll keep that in mind henceforth. –  aniruddha-ix Oct 9 '12 at 7:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.