How to avoid volitile functions in hyperlinks

[Skysurfer]

Hi,

I am using the hyperlink formula below in a table of contents. In one column, I have 50 range names and in the next column hyperlink formulas that reference those range names. Once the range names are created, I simply copy my one hyperlink formula down alongside the range names.

Thing is, my formula uses a LOT of volatile functions. Is there a way to do this without so many INDIRECT functions? Or does is this type of formula not so resource intensive to even worry about it?

=HYPERLINK("#'"&MID(CELL("filename",INDIRECT(C13)),FIND("]",CELL("filename",INDIRECT(C13)))+1,256)&"'!A1",MID(CELL("filename",INDIRECT(C13)),FIND("]",CELL("filename",INDIRECT(C13)))+1,256))

Thanks,

Lawrence

 

[Leith Ross]

Hello Skysurfer,

Depending on how many links you have, it may be nothing to worry about in terms of performance. If you do notice that is slowing down the workbook then you may want to use a volatile UDF macro to create the hyperlink addresses instead.