Excel Data Organization: Array Formulas? Tables? Named Range?

Question

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?

Answer 1

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.