It is volatile becuase you are asking Excel to interpret a string as a reference. This means, to Excel, that the string could change at any time, so must recalc every time there is a change in the workbook. This slows things down when you have lots of INDIRECTs.
Can you provide the basic structure of the other worksheets? I assume they are all the same, just different data. If so, it might be beneficial to have them all on one table and then have a monthly summary sheet or something that displays the month's data using formulas and a selection tool of some sort. This will limit the number of sheets and eliminate the need for INDIRECT.
Also, Can I see your VBA code? Here is how I would write it, not knowing exactly what you need it for.
Oh, and want to learn another trick?
This will give you the end of month for the month prior to A1. EOMONTH works just fine, but this is another trick. You can do things like "=Date(Year(A1),Month(A1)+6,0)" to shift months around. I find it easier to read than EOMONTH, but that is just a preference. It also works on all versions of Excel.
Bookmarks