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.

We have a pretty unique situation at work. We have order takers who take orders and each individual references the same shared read-only excel document on a network storage drive. This excel document contains multiple sheets that has prices and other formulas that contribute to correct pricing for a customer's order. Along with those sales prices there are sheets that contain reference information, etc.

Certain places where the orders are being filled only have so many resources available. We would like to include in this shared, read-only spreadsheet information that will automatically update without having each user close and re-open the excel document.

It is my understanding that you can specify a data source (such as a database or a web document, etc) to pull some of this information directly into the excel document every few minutes. I have thought about creating a local web page to feed data into the main excel spreadsheet which is on a network drive (If I do it this way I have no idea what programming language to use).

We are running Office 2003 (still) but have Office 2007 on some computers. Additionally most of the computers in the office are Windows XP and we are close to migrating everyone to Windows 7 64-bit machines.

Summary:

  • Automatically push information into a shared, read-only excel document.
  • The excel document is saved as a 97-2003 .xls file.
  • The computers are all currently running Windows XP, upgrade coming in a few months.

What is the best way to set this up? Any suggestions without having our office re-invent the wheel?

share|improve this question
    
How complex of a data set are we talking about here? Given what you have so far, you are likely better off using Access with a custom UI or sqlite with a simple web interface, or really any database for that matter. I would really drop the idea of using Excel any longer as it is not designed for this type of use. –  MaQleod Jul 10 '12 at 22:39
    
The excel spreadsheet contains formulas and relevant info for the order takers, we will be unable to leave excel. However, I have been thinking about using Access - the reason I liked the HTML Web options is because I know it automatically refreshes and pushes new information out from the data source for however long you specify. –  cyborgcommando0 Jul 10 '12 at 22:45
    
Anything in excel can be recreated with a programming language, it is just a question of the type/format of the data, speed at which you need access and potential for growth to decide if it should be done. Spreadsheets are not very scalable - it is difficult to modify one when there are changes in a business model. Databases are designed with scalability in mind and are meant for high-demand, quick-changing, multiuser environments. Sometimes it is better to reinvent the wheel than to hold on to limited technologies. The more you build on Excel, the harder it will be to expand when you need it. –  MaQleod Jul 10 '12 at 22:54
2  
I think you should look into using a real database rather than using Excel as a database. –  Head of Catering Jul 10 '12 at 23:46
    
if you are open to a web connected database, you can do everything you need without excel. All the pricing can be pretty complex, and immediately accurate with live data –  datatoo Jul 11 '12 at 9:36
show 4 more comments

1 Answer

Using datasources in Excel you can specifcy MS Access,Web,Text files,SQL for external data.

When at the end of the wizard, just click properties and select the following:

  • Enable background refresh
  • Refresh every [x]minutes

Select Properties Properties

share|improve this answer
add comment

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.