consolidating the results of multiple scenarios

I have a model that outputs a multi-year profit & loss forecast for one of x projects depending upon input into a project number field. For example, if I wanted to analyze project 1, I would type 1 into the project number field. I'm trying to figure out how I can create a consolidated p&l summary of all the projects that will most likely be viewed as a pivot table. I'm trying to minimize the amount of manual copying and pasting.

For each of the desired output fields, i.e. RevAY1, RevAY2, ExpBY1, ExpBY2, etc., I created a list in column A with values 1-29. In column B I refered to the appropriate cell from the summary p&l. For example, if value 1 is year 10 expense for product A, I refer to the cell on p&l that corresponds to ExpAY10.

In order to automate the retreival of this information, I have a few two-way data tables that take row input as the project # and column input as a field that takes a numeric value (1-29) corresponding to the above table and using the choose function returns the appropriate value. Since it appears that the choose function has a 29 option limit and I have far more than 29 fields on the p&l statement (number of revenue and expense categories and 10 years of data), I have to use multiple data tables. After running each data table I copy and paste the values into another table and base either my pivot table or lookups off this master databasee. However, this process is time consuming, not only b/c of the number of data tables, but also, b/c it needs to be repeated anytime a change is made to the underlying p&l.

Any help figuring out how to make this more routine and quicker is greatly appreciated!

I guess reporting on projected results is no different to reporting on actual results. Reporting on actuals is easily done with a pivot table .. but most easily if the data is in a form like the transactional data from a ledger.. eg

date, account, project, value, etc.
1/1/2006 12345 ABCD 100.00 ....

Once you have data in that format you can automatically add additional grouping information in helper columns either by string manipulation, vlookups from another table or similar approaches.

If the only way the model can create the data is with lots of columns (eg one per year per account code) then you can attach to that file with msquery and use a union command to turn it into columnar data in the from shown above.

regards..