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!
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!
