Can a Chart dynamically change it range based on a cells value?

I am not sure where to post this question so I thought Charting would be a good start.

I have a chart with the formula below.

Please Login or Register  to view this content.

If I have a cell lets say B10 on a worksheet automatically changes. Can the chart as defined below do the follow. If B10 is has "NID" in it the chart plots one less data point.

Please Login or Register  to view this content.

If that cell does not contain "NID" then it plots one more data point:

Please Login or Register  to view this content.

I am not sure how to best accomplish this task automatically. VBA code formulas???

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

You can try with PowerQuery (PowerQuery & PowerPivot - add-ins for Ex2010) and Worksheet_change to refresh query connection automatically. So at the end anyway - vba
or
maybe someone else will give you another solution

Dynamic Named Ranges:

AbsissaVals =Sheet1!$D$2:INDEX(Sheet1!$2:$2,1,COUNT(Sheet1!$3:$3)+4)
chartDATA =Sheet1!$D$3:INDEX(Sheet1!$3:$3,1,COUNT(Sheet1!$3:$3)+4)

(the ranges expand by one if there is a value in the benchmark cell)

Please Login or Register  to view this content.

IF D5 = YES, 1*(Sheet1!$D$5="YES") = 1
IF D5 = NO, 1*(Sheet1!$D$5="YES") = 0