Design suggestions for dynamic data table design for portfolio evaluation model

[estephenkim]

Hello All,

I'm developing a tool to be used as a weighted-average portfolio evaluation scorecard on which I could use some design advice. I've enclosed screen captures [will upload later] of a sample model that illustrates the tool design.

Overview:

I have a data entry worksheet and multiple evaluation worksheets on which separate reviewers will independently rate each investment. The investment data entered into the Data worksheet is designed to copy to the Eval worksheets using a simple cell reference (e.g., =Data!A8).

Objective & Problem:

During each evaluation cycle, there are varying quantities of investments, therefore I want to design this tool to accommodate quantity changes each cycle. I plan to password protect the worksheets and allow row additions to accommodate changes in investment quantities, but my problem is that when the user adds rows to the Data worksheet, this doesn't change the structure of the Eval worksheets in response. I'd like to design the tool so that the only manual task required of the user preparing the workbook is to enter the investment data in the Data worksheet.

Question:

Is there a way to relate the Eval worksheets to the Data worksheet so that any changes made to the data table are reflected in the Eval worksheets so that investment data entered in the Data worksheet are copied exactly to the Eval worksheets?

Thanks for your ideas and suggestions,

Steve

 

[estephenkim]

Here are the screen captures:

Data: Data entry worksheet




Eval 1: One of potentially multiple evaluation worksheets

 

[estephenkim]

Any ideas? Could use your help.