In attachment is an example file I composed based on picture you posted - at least as much as I understood what you want to do.
As you use Excel2003, I used Named Ranges/Dynamic Named Ranges to make the tables dynamic - i.e. you enter new data, and all formulas will adjust automatically.
Had you newer version of Excel, I'd used defined Tables instead.
The 1st sheet has the structure like yours, i.e. for every new year you add new columns into table. The problem with this setup is, that you have to edit the table every year - add new formulas. And this table will be a nightmare for you, when you need some report over several years, or over whole table grouped by some other parameter.
Next 2 sheets are for advised structure of same data. I didn't know what kind of data you have before column P, so I guessed, that there is some info which is repeated for every year. So I created e a separate sheet where you register such data once, and can read into data entry table using formulas whenever you need this.
The main idea for advised structure is, that you have a single column for certain type of data. You then can design report sheets, where user selects parameter(s) for report, and the report is created by formulas using entered parameter(s). Or in simpler cases, like yours, you can use autofilter feature and SUBTOTAL() function to get the wanted result (but with much more flexibility compared with design on 1st sheet).
|