Excel 2007 : Make the INDIRECT formula non-volitile

Hi everyone,

I love the INDIRECT formula. It solves so many accuracy assurance problems and is so simple. One problem, my excel spreadsheets are too big to handle a lot of INDIRECT formulas, because the formula is volitile.

Does anyone know of a way to make the function non-volitile. ie make the formulas not recalculate anytime I change any cell in the workbook. Is there a similar VBA formula I could write to mimic the INDIRECT formula?

Thanks

What you can do is turn off automatic calculation and then specify when you want it to calculate and what to calculate.

Lot of times INDEX formula can replace INDIRECT.
Can you upload example workbook and explain what you trying to do?

I run a pricing model for a financial company. There are a lot of products, each product has its own variables (named ranges). I want to use the INDIRECT formula to lookup to one cell to determine which product variables to use. Each product table offers different interest rates. Say I have a product called product1. (apologies for the anonymity) To determine the price of product1 at various interest rates, i have to add

vlookup(rate,basepricetable_product1,2,false) - looks up base price from table
fee1_product1 - named range of fee 1
fee2_product1 - named range of fee 2
Adjustment1_ - named range of adjustment

Heres a sample price table, starting at cell A1.

Product1
Rate Price formula
3% vlookup(A3,INDIRECT("basepricetable_"&A1),2,false)+INDIRECT("fee_"&A1)+INDIRECT("fee_"&A1)+INDIRECT("adjustment_"&A1)
3.125% vlookup(A4,INDIRECT("basepricetable_"&A1),2,false)+INDIRECT("fee_"&A1)+INDIRECT("fee_"&A1)+INDIRECT("adjustment_"&A1)
3.25% vlookup(A4,INDIRECT("basepricetable_"&A1),2,false)+INDIRECT("fee_"&A1)+INDIRECT("fee_"&A1)+INDIRECT("adjustment_"&A1)


IF I want create a new price table for another product, I can copy the one I already have, and simply change the product name in cell A1.

Works wonderfully, but since I have over 50 products, the INDIRECT formulas kills me with the constant updating.

And can you upload example workbook?