Dynamic Range in Column Chart That Ignores Blank and With-Formula Cells

Hello gurus,

I just want to seek yet another help with regard to creating a dynamic chart with named ranges.

As you can see in the attached workbook, I was able to generate what I want to achieve however, the problem surfaces as even the blank and formula-driven cells are also included in the chart plotting. I do not want these to appear, and leave those with values in the chart, as entries will vary depending on the selection (i.e. value on ='Filtered Table'!$A$1)

Chart Issue.xlsx

I have done my own research but the solutions are generic and are not addressing my unique concern.

Thanks in advance for your help guys.

Like this?

DynamicSeries.xlsx

You're awesome guys! Thanks for both of your inputs. Both worked great! Rep point to both of you!

Note your "Database" range starts in row 2 but ranges in formulae start in row 3

=INDEX(Database!$G$3:$G$100, SMALL(IF($A$1>Database!$D$3:$D$100, ROW(Database!$D$3:$D$100)-ROW($A$3)+1), ROW(1:1)))

????


by changing the formula as follows you could simply drag them across the columns

=INDEX(Database!A$3:A$100, SMALL(IF($A$1>Database!$D$3:$D$100, ROW(Database!$D$3:$D$100)-ROW($A$3)+1), ROW(1:1)))

Hello again gurus,

Though all your solutions meet my preference (esp @cyiangou) however, I cannot seem to apply it to the file I am currently working on. I'm not sure where I am missing in the process, but the named ranges I am assigning to it do not allow me to proceed and pops out an error.

Please note that I am working on with a macro-enabled document as a whole, but I guess it is not the reason why I am experiencing the error.

I have been draining my brain cells out yet to no avail. I will appreciate if you can help me again with this file (attaching the file which is apart from the confidential info cleansed out, it is the exact row and column arrangement)

Thank you again for all your inputs and help!

SAMPLE.xlsm

I don't know what exactly I did different, but I was able to fix it myself!

Lesson learned: Stretch my patience more!

Thanks anyway guys!