Variable Formulas

[mbshafe]

Thanks to these boards, I've been able to enhance a spreadsheet quite a bit, however, it just occurred to me that I've been asking all the wrong questions. To get right to the heart of the matter:

I need to create a sheet that contains many different types of asset classes. One of the things I need to calculate on this sheet is current market value. The problem is that the formula to calculate the MV is different for each asset class. So I'd like to create a sheet within the workbook that defines the structure of the formula for each and then properly applies it.

Here's an example of one of the formulas:

=INDEX('Position Detail'!$1:$52,MATCH(Deals!A4,'Position Detail'!$B$1:$B$1000,0),MATCH(Deals!O2,'Position Detail'!$A$9:$AA$9,0))

So far, there are two other formulas that could apply depending upon the asset class...does anyone have any advice for me? TIA, Mike

 

[Derek Brown]

Have a look at the INDIRECT function - you can use a value in a cell as part of the formula itself.
Note that it is a 'volatile' function (http://www.decisionmodels.com/calcsecretsi.htm) so too many will slow down your calculations.

 

[mbshafe]

Thanks!