OFFSET, COUNTA or SHOULD BE COUNTIF Function for dynamic range for Chart

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

You can use the formula =INDIRECT() to have the range applied within one of your formulas dynamic.

Ex (if you enter 500 in cell B1 the following formula will count the range N18:N500. As soon as you change the value in B1 to 520 it will be counting N18:N520)

Please Login or Register  to view this content.

If that doesn't answer your question you should upload an example worksheet with sample data and desired result entered manually

Hi ThirdFret,

Thank you for your response.
I wanted to attach the file previously but for some reason it couldn't work.

But it did now (see below).


Journal.xlsx

Thanks!

Teddy

Hi ThirdFret,

Tried adjusting the formula with INDIRECT as per your provided code.
Can't work around it.

Look forward to help.

Thanks,

Teddy

Sorry I didn't get to look at your problem over the weekend. Attached is a version of your example sheet that I think does what you're hoping to achieve.

Charts don't like named ranges using INDIRECT, I guess. The attached solution names the dynamic ranges without using INDIRECT.

ex.

Please Login or Register  to view this content.