Dear Experts,

I'm new here and have found this forum very interesting.

I need to present a dynamic chart featuring a dynamic range as I add on new data in specific columns.
I'm however, unable to do so as my column cells may be empty but is filled with hidden formulas.
This always makes it count the last set of column cells that I have created (at 500th row) instead of the latest cells reflecting actual numerical values.

Please advise how to edit my formula and allow the chart to show latest "X" number of cells in a column.



Formula for Dynamic Chart under Defined Names: =OFFSET($N$18,COUNTA($N:$N)-($X$2+1),0,$X$2,1)

$N$18 - the column and the cell where my first numerical value will begin.

COUNTA ($N:$N) - the Column N where all figures will be counted (as per my period value set in another cell named "X2").



Note:
1) There is a period setter such that I can choose how many cells in one particular column to reflect on a chart as latest "X" number of information.
2) My chart formula for OFFSET and COUNTA is under "Defined Names".

Looking forward to your responses!

Thanks!

Teddy