Indirect to non-volatile

[pto160]

I am just wondering why Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.

 

[pto160]

I downloaded the zip file. Where do I copy the formula to test for volatility?

 

[DonkeyOte]

Using VolatileFuncs.xls - place your function of interest in any cell and then use:

=CalcSeqCountRef(cellwithformula)
(change that in red to be the cell reference containing the formula you're testing)

If you press F9 and the number generated by the above changes then the implication is that Function is Volatile - if not Volatile the value will remain static.

 

[pto160]

Great. So I tested my formula and it looks like it is non volatile.

 

[jamesjts]

So how would I make this formula work using what you have here

=HLOOKUP('400'!$F$1,'400'!$J$1:$M$2,2,FALSE) - (400 is the name of the sheet and is contained in column A)

=HLOOKUP(INDIRECT("'"&$A1&"'!$F$1"),INDIRECT("'"&A1&"'!$J$1:$M$2"),2,FALSE) - (using Indirect/ does not work for me and generates #ref error)

Yes

A very simple example:

A1 = x
A2 = y
A3 = p
A4 = v

You want to create a dynamic range for column A.

=$A$1:INDEX($A:$A,COUNTA($A:$A))