looping through a set of calculations

[Guest]

Hi,
I had posted this question in the General section but thought that it may be
more appropriate here ...

I have a number of financial calculations that need to be repeated for a
list of dates where the result is dependent on the input date. I need the
results form all these dates for other calculations in the spreadsheet.

I have set the calculation to manual since the spread sheet is quite large
and use F9 to recalculate.

I used the Data - Table function to repeat the calculations for the set of
dates. However, the Table does not recalculate when I hit F9. The bottom
left portion of the Excel window says "TABLE" when I hit F9, which I guess
means that the Table has not re-calculated. I have to go the the sheet that
contains the Table and hit Shift-F9 to force the Table to recalculate.

How can I force the Table to recalculate when I hit F9, or is there a better
way to perform a repeating loop in Excel?

Thanks

Anand

 

[Niek Otten]

Hi Anand,

It ought to work that way and it's a good way to do it.
Are you sure the data in your table depends on something that has been
changed? Do you not have any User Defined Functions that take data from
sheets directly instead of via arguments?
Sometimes (but very, very rarely) Excel loses track of dependencies. You can
rebuild the dependency tree by pressing CTRL+ALT+SHIFT+F9.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

Niek:
Thanks for you quick response. You were right, it is working. But it does
seem slower...

when I hit F9, I first see "Calculating xx%" in the bottom left of the
screen, then it displays "Table: 1" for quite some time after which it
displays "iter x" and then finally "Ready"

When I saw the "Table 1" displayed for some time, I thought that the program
had got stuck at that point.

Does the Data table make the calculations slower than if I physically copied
the calculation section of the multiple times?

Thanks,

Anand