Indirect() Function Changes to #Value when a second sheet is opened....

[SQE]

Good evening,

I have a sheet that works great... it has a couple hundred indirect functions like this that pull budget information from multiple tabs. I refer to the tabs by number so I can re-order them and the main budget sheet with the indirect formulas plays right along.

=INDIRECT("'" & sheetname(5) & "'!a26")

Here is the problem... any time I open ANY other sheet that has a formula in it, every indirect function changes to #Value until I go in and force the sheet to recalculate. This happens any time a formula is operated in another sheet. It makes it almost impossible to be working in multiple sheets.

Anybody have any ideas on how I can fix this, or another potential way to do this?

I uploaded a screen shot of it so you have a feel for what I see.

Thank you, in advance.

sqe