Calculation table working with circulars

[JamesMc007]

Hi all,

New to this board, but got a problem I'm hoping someone here can help with.

I'm working on a financial model which has a circular formula in it, and unfortunately, there is no other way to do this calculation without using a circular - in this case it's a necessary evil.

We also need to have a calculation table, which does not work with having a circular at all.

I'm trying to find a way to make the two place nice with each other and have thought there might be a way using macros. Is there a way to control the rebuilding of calculation tables? For example, to run a macro which would make the circular calculation for each column of the calculation table?

Any ideas would be much appreciated.

 

[sanrv1f]

Hello and welcome to the board,

Using a macro you could convert the circular cells to values, but it would be better, if you post some sample data and expected results

 

[JamesMc007]

To give a little more info, the calculation table would be a 1-variable table, to calculate the model at various revenue levels. The circular formula would need to be run at each level of revenue.

I was thinking about having the the macro paste as values the result of the circular, but it didn't work. I wasn't able to figure out how to get the macro to run at every column of the calculation table. I put the code in worksheet_calculation, to run when the cell that the calculation table uses as its 1 variable, but the macro didn't show it changing at all.