conditional statement for Excel Chart

I am trying to create a chart that uses a different set of data range depending on a condition.  I am creating a monthly report, but do not want to show zeros for the current month for the future dates.  I was using if statement and vblookup to keep future dates blank but they are showing zeros in the graphs since its not really an empty cell.  is there a way where I can use a conditional statement to for a chart to use a different data range based on a conditional statement
kbryant8243Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
For your future dates force it to return an error rather than zero or blank. The chart won't display an error value.

=IF(True, Value, NA())

Thanks
Rob H
kbryant8243Author Commented:
using a bar graph, tried that and N# showing up as values in graph
Rob HensonFinance AnalystCommented:
Is this in the data or the headings? I had assumed data but sounds like it is for the headings/axis labels.

Another option is to hide the rows/columns that contain future dates. The hidden rows/columns won't appear in chart even if included in the data range.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
psteffCommented:
Depending on the way your data is structured and what you are trying to do for sure, here could be some other possible ways of doing it:
1. Use dynamic named ranges to only include the data that you want.  The dynamic named range would probably need to be set up with the offset function and perhaps the countif or index/match functions.  If you do a search on the web on dynamic named ranges, you should be able to find some good information on it to suit what you are looking for - or
2. Create a pivot table and use it to filter only the months of data that you want and then create the chart on the pivot table.
kbryant8243Author Commented:
thanks for the help was able to do it by doing a custom label format
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.