Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to make a huge Excel sheet reasonably maintainable, but it's huge in the "hundred-table-db" direction, rather than the "hundred-thousand-row-table" direction.

I want to have a baseline data table that looks something like this:

| Indicator                | Units       | 2010 | 2015 | 2020 | 2025 | Source     | 
| GDP                      | $Gazillion  | 300  |  350 | 400  | 450  | BLS        |
| Population               | Millions    | 350  |  400 | 450  | 500  | Census     |
| PetMonkeyPopulation      | Thousands   | 50   | 60   | 70   | 80   | SimiansRUs |

And then be able to have another sheet that looks like:

|                          | 2010 | 2015 | 2020 | 2025 |
| MonkeysPerCapita         | .1   |  .2  | .3   | .4   |
| MonkeysPerDollar         | .01  |  .01 | .01  | .01  |
| GDPPerCapita             | 300  |  400 | 450  | 600  |

Is there some standard way to make this kind of thing maintainable?

share|improve this question
add comment

2 Answers

I hate to post a non-answer, but you really should move this to a true database. It will be more stable, faster, and easier to use and update. Then (or if I've assumed wrong and it already is in a database) you can set up queries that Excel can use to pull relevant data or calculations into a spreadsheet for reporting or manipulation.

share|improve this answer
    
agree! .... and my 15 characters with me :) –  ldigas May 6 '10 at 19:01
add comment

I'll agree with the other respondents; you're talking data and metadata, which really are for databases.

But, if you're bent on (or limited to) making it work in Excel, the only thing I can think of to do is to logically break up your single sheet into multiple sheets for what would be your data tables if you had them. This could be similar to an ER Model (http://db.grussell.org/section006.html).

So, for variables that change frequently (such as daily sales data), keep them in a main data file sheet. Other variables that don't change frequently, but are part of the main data (like names of sales reps) can go into other files (or other sheets - this is your metadata). This workbook in totum will be your "database." (You're basically creating relationships among multiple flat files - this is actually how the very first databases worked.)

Make sure you keep at least one common field that's a unique identifier between at least 2 sheets (i.e. if you have a sheet for SalesRepData that has SalesRepID and Territory on it, and you have another sheet for TerritoryData that has Territory and CityNames on it, you can then derive sales rep per city between the two sheets onto another sheet).

When you make a report that requires data from multiple sheets (or files), you will then use vlookup and index/match functions to link your sheets.

Finally, for the sake of documentation and audit, which is often overlooked, you should also create a master document that desribes the content of each of the sheets and how each sheet is linked to the other.

Good luck!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.