Thread: Dynamic charts

Hi,

To make the charts more dynamic I was using the following.

=OFFSET(Charts!$A$7,0,1,1,COUNT(Charts!$7:$7))

I was having some recalculating problems with the workbook and thought it was due to the Offset, but I have come to find out it was something else. Well in the meantime I have changed the Offset reference to the below and it seems to work fine.

=Sheet1!$A$7:INDEX(Sheet1!$7:$7,MATCH(BigNum,Sheet1!$7:$7))

Just looking for some advice from those who have the knowledge/experience! Should I stick with the Index or go back to the Offset?

Hi,

Index is non-volatile whereas Offset is volatile. Therefore the formula you now have is more efficient.

I think that given the way names are evaluated the OFFSET is ok - ie the Name is only evaluated when the cells utilising the name are themselves evaluated so it is their volatility that is key (at least that's my understanding).

INDEX is preferable if you have blanks interspersed in your data - eg if row 7 contains

#, blank, #, #N/A, #

the INDEX will cover the appropriate range whereas COUNT based approach will not