I inherited an Excel spreadsheet with a large number of rows which grows monthly. I expect it to reach the 66536 limit by early 2015.The problem is that we have pivot tables from other sheets that all references this sheet.Does anyone have any suggestions or recommendations? I know it's a mess but it's what I inherited.

ouch - database time ?

Database would require a big overhaul unfortunately. =(

Upgrade to Excel 2007?

QuoteOriginally posted by: blackscholesDatabase would require a big overhaul unfortunately. =(How bad does it have to get before someone decides to 'overhaul'?What does the operational risk have to be before someone 'overhauls'?How much time has to be wasted working around things that should not be an issue before it is time to 'overhaul'?How difficult is it, really, to incrementally upgrade this 'tool'? First step?Load it all into a sql database.Write a function in VBA that pulls the necessary subset of the data required for a computation into a table.(better would be to write a dynamic view in database that will do this, and not have inline SQL in the VBA... but almost anything is probably better than what you have now).Smart people solved all of these problems years ago. So why not just do it properly? BTW: 'overhaul' should be a constant feature of your workflow that is accounted for in project time (whereupon it would known as 'refactoring'). Otherwise you can expect a gradual increase in entropy until you hit the singularity where everyone just spends all of their time solving/working-around the problems that they and their predecessors created. Furthermore, any code/tool that is too big or complicated to be easily 'overhauled' in a modular fashion runs a real risk of also being to big and complicated to actually produce the intended results. And no one will know until it is too late because no one wants to 'overhaul'.

QuoteI inherited an Excel spreadsheet with a large number of rows which grows monthly. I expect it to reach the 66536 limit by early 2015.This means you have a year to estimate, schedule and execute this migration. I assume you have a budget from management for this?I think some big decisions need to be made1. Choice of database (e.g. SQL Server)2. Access technology (e.g. ADO.NET)3. Language (e.g. C#)Personally, I would move away from VBA and ADO (or is it DAO still?)Testing will be a challenge.Quote BTW: 'overhaul' should be a constant feature of your workflow that is accounted for in project time (whereupon it would known as 'refactoring'). Otherwise you can expect a gradual increase in entropy until you hit the singularity where everyone just spends all of their time solving/working-around the problems that they and their predecessors created. Indeed! But how to convince management? It's a universal problem.

QuoteOriginally posted by: CuchulainnQuoteI inherited an Excel spreadsheet with a large number of rows which grows monthly. I expect it to reach the 66536 limit by early 2015.This means you have a year to estimate, schedule and execute this migration. I assume you have a budget from management for this?I think some big decisions need to be made1. Choice of database (e.g. SQL Server)2. Access technology (e.g. ADO.NET)3. Language (e.g. C#)Personally, I would move away from VBA and ADO (or is it DAO still?)Testing will be a challenge.Quote BTW: 'overhaul' should be a constant feature of your workflow that is accounted for in project time (whereupon it would known as 'refactoring'). Otherwise you can expect a gradual increase in entropy until you hit the singularity where everyone just spends all of their time solving/working-around the problems that they and their predecessors created. Indeed! But how to convince management? It's a universal problem.i) You work out how much longer it takes everyone to do everything because the systems are crap and workout the cost to the firm of this. You also highlight the additional operational risk by illustrating with some choice gory examples (of which there are many).ii) You work for a firm that already 'gets it'

As T4A has pointed out, this is a non issue. Recent versions of Excel are no longer size constrained and limited only by available memory.

QuoteOriginally posted by: DavidJNAs T4A has pointed out, this is a non issue. Recent versions of Excel are no longer size constrained and limited only by available memory.Its not a non-issue because it is still a terrible way to store and query data.

QuoteOriginally posted by: neuroguyQuoteOriginally posted by: DavidJNAs T4A has pointed out, this is a non issue. Recent versions of Excel are no longer size constrained and limited only by available memory.Its not a non-issue because it is still a terrible way to store and query data.Yeah, that 2007 is kicking the can down the road, yes?Excel is the wrong tool here.