Excel/Sharepoint Query

I hope I'm putting this in the right place.. it's a general windows question, but regarding specific applications.

My team keeps a rather extensive constantly updating excel sheet on SharePoint 2010.

The problem I'm trying to address is the fact that it can't be queried without actually opening the doc in share point. While searching in excel isn't to big of a deal, I am looking for a way to stream line this, so less people need to constantly be opening the doc.

One idea I had was to link it to an access database, and somehow query it from there (part of me believes we should move it to access anyway.. our columns are beginning to have columns it's grown so crazy)

Does anyone have any idea's or similar uses cases? Ideally i'd want to be able to query a line from the sheet, without needing to open a sharepoint session.

Alternatively, I'd like to know if i'ts possible to link an excel sheet to an access database.. and use some sort of query tool against the database (on share point ideally.. for global fix.. locally just for my personal sanity) without having to open it. This would allow me to get more information then a single row.

Sorry if this seems a bit convoluted.. I'm not very experienced with this level of excel/access application usage.. and very inexperienced when it comes to share point.


Thanks!


~Denny

It sounds like your situation is pretty complicated, but here are some general approaches you could look into:

Clean up the spread sheet so that is "flat" no columns in columns, basically just a csv file. Import it into Sharepoint as a list. (this may have poor performance if your dataset is very large, but limits on queries/view can help with that.) You can then link to the data from Excel (for pivot tables) or from Access to run advanced queries.

Get it out of Sharepoint and into access. If you're getting columns in columns it sounds like you really need multiple tables joined together in queries.

You can use an excel file as a datasource in Access (or another Excel file for that matter.) You may even be able to use a webDAV url rather than a local file url so you wouldn't need to open the xls first. But this approach may be ill fated with multiple people accessing the file frequently, I can see a plague of file locks and check out issues.