Connect Large 400k Row Excel 2010 Set to Excel 2003

[analyst44]

Hello there!

I haven't posted here in a long time but have certainly searched through here over the months to get help on any number of things. Thanks to the community for that. I love this place!

My current dilemma is as follows:

I'm in a corporate environment where Excel 2003 is the standard, though they will ultimately migrate to 2010 sometime in the near future (supposedly). I was brought in to help them aggregate a lot of disparate data sets into a master database, clean it up and then allow for some ad-hoc and traditional reporting. As of now, the database is over 400,000 rows.

As I think about making some user-friendly reporting tools, it makes sense to design those in Excel 2003. However, I'm not sure what my options are for connecting the data source from one to the other.

I was under the impression that I could create pivot tables from the 2010 Master Data Set and house those (cache and all) in 2003. However, when I try to do this, I get an error telling me that the external data source is not in the right format. I'm assuming this is because I have an additional sheet in the Excel 2010 workbook that contains some (needed) formulas that are used in the database. This is where I'll note that this Master Data Set is not a true "database." It's structured like one, but it also has some formulas that need to be in there (at least, based on the way I've structured it).

So, I'm not sure how to proceed. Ultimately, I just want to have access to pivotable data in Excel 2003, but I don't want to maintain another Master Data set somewhere else if I don't need to.

The one thing I could think of is perhaps moving the worksheet that has the formulas to its own workbook, which might then allow the external data source to recognize the one-worksheet book as something that can be pivoted.

I am open to trying this but wanted to see what my other options might be.

Thanks so much for your time!

 

[cjcobra]

Can you dump it into Access and run a couple queries to get to the 65k Excel limit?

 

[analyst44]

I *can* do that, but I will then have to maintain the data in two places, which is far from ideal.

The data in Excel requires periodic changes, additions and subtractions, so I'd like to be able to query it/pivot it directly from the source if at all possible.

 

[analyst44]

The other option that I'm mulling is to simply do the entire design of the reporting tool in Excel 2010 with some macros that will just output the reporting into Excel 2003. This gives the users less to work with in terms of interaction, but I think it will suffice as we have pretty good specs on what they need and the intervals that they need it on.