consolidating 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 (multiple lists of 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!