Excel function, exclude dates in chart data source, excel 2010

[jrwrita]

I am forming a chart from sheet A. Sheet B contains all my data.
I want to exclude a specified date(s).




Sample data:

+---+----------+--------------+--------------+-------------+-------------+-------------+
    |   |    A     |      B       |      C       |      D      |      E      |      F      |
    +---+----------+--------------+--------------+-------------+-------------+-------------+
    | 1 | Date     | 29/03/2017   | 30/03/2017   | 31/03/2017  | 03/04/2017  | 04/04/2017  |
    | 2 | Number 1 | -594590.4649 | -636666.4504 | 795637.1614 | 842563.4322 | 496463.9301 |
    | 3 | Number 2 | 2189587.44   | 1301681.418  | 2080839.353 | 1945335.214 | 2421728.123 |
    +---+----------+--------------+--------------+-------------+-------------+-------------+

The final output would be me excluding 30/03/2017 , and keeping the rest in my data selected for my chart.
the issue is that I want to maybe exclude a date in the middle of my selected range. But since this may be a hassle to input a long formula each time into my data selected. I would like to see if there is any formula/function to eliminate a specified date/column. Perhaps manually enter the column you want to exclude in a formula.


My current range is something like

=Graph!$AB$5:$KA$7

But is there a function to exclude one of these columns?


I can manually select which dates with <kbd>Ctrl</kbd> but seems tedious.

 

[VBA Geek]

Hello, what about just rearranging your data and then add a pivot table and pivot chart where dates can be easily filtered out?

something like:

 

[jrwrita]

Thing is my data is horizontal like shown above, its kind of hard to manipulate as it is a work contraint . I kind of just wanted a user-defined function to exclude a date from them.

 

[VBA Geek]

it is actually very easy to rearrange it by using Multiple Consolidation Ranges. Google multiple consolidation ranges

 

[jrwrita]

Thank you, ill give it a try, was really hoping for a formula of some sort.

 

[VBA Geek]

Another soluton for you is to set up the data like below. Then listing the dates you want to exclude down column H and in cell B5 you enter the formula: =IF(ISNUMBER(MATCH(B$1,$H:$H,0)),NA(),B2)

and the series for number 2 will be: =SERIES(Sheet4!$A$6,Sheet4!$B$1:$F$1,Sheet4!$B$6:$F$6,2)
and the series for number 1 will be: =SERIES(Sheet4!$A$5,Sheet4!$B$1:$F$1,Sheet4!$B$5:$F$5,1)

by the way in my previous post the dates in the chart have been sorted largest to smallest, you may want to correct that if you go down that route, dates should go from oldest to newest

 

[jrwrita]

Thanks for this! this might do the trick. For series do you mean chart range?
Also can this be done without duplicating the data beneath and instead just listing dates to exclude in col H?

 

[jrwrita]

Seems like I am able to do the trick by duplicating the data with your formula =IF(ISNUMBER(MATCH(B$1,$H:$H,0)),NA(),B2) and inputting data in col H, then for selecting the chart data i just alter the range to include the dates and new data.
Not sure what the series is okay.
I am now realizing I just need to be able to duplicate the with exclusions.

 

[VBA Geek]

the range for series is as per below: