Recommended implimentation

[oworld]

We are putting together a multiple page Excel Workbook. However, you know that in order to have dropdowns you have to have the data in a page as a Named Range or Lookup. This is hard because you are using like a database. And they can be large. Imagine if you download a template and you have to put all the 5000 rows of information in a column as a named range. So I told them yesterday that maybe we should use an external XML file and read/write data from the Excel sheet so that it would not be as large and then they can actually interactive with Webservices to update data. That is all fine and dandy assuming they are smart enough that when they copy there spreadsheet to the laptop to go out in the field, they remember to also copy the XML file(s). And then there is setting a minimum Excel version so that they can make use of XML, Webservices, etc. So, this lead me to thinking about Access because much is built in. Keep in mind that most of their users use Excel so they want to stick to that and leverage it. I could write a .NET win application, but then we would have to have the .NET runtime. And I am not sure yet whether they have a framework that runs on non-MS machines. So you can see the delima. How to leverage their current use and need for Excel while making it a bit more powerful to do live updates and hold there own data stores.

Any suggestion would be greatly appreciated. The idea here is to allow them to have spreadsheets that can hold and calculate data but be able to interactive with webservices much like Microsoft Update so if there is new items for the dropdowns that would be updated to them.


C

 

[gwkenny]

Excel

If you are just concerned about having the correct data in the named ranges for your drop down boxes...

You can save the lists that should be in the drop down boxes on a website in text files.

When Excel opens, it tests to see if it can find those text files on your website. If it doesn't execution continues.

If it does find the files, it can see if it has the most up to date data in its named ranges for your drop downs. If it isn't, open the file and update the data for the drop down boxes.

It's my suspician though that this isn't your best solution. I don't know what your business objective is with this "spreadsheet" but sometimes you have to drag your customers kicking and screaming into the 18th century. errr 21st century, err, whatever time we call modern.

 

[oworld]

I am interested in your solution here about having the files in text files on the web. Do you have any examples of this? I admit that I really don't like Named Ranges because I believe these should really be more of a data piece not visible to the user and in a combobox. I am just not sure how Excel would do this. So any help would be appreciated.