Expand Defined Name Range Horizontally to Dynamically Change Chart with new data

Hello Everyone,

I am working on streamlining a report. With each succeeding new month, new data is placed into my workbook horizontally, according to month. The data is ten used to populate a bar chart comparing the current year and SPLY performance for each month. The data is currently organized as such:
BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV
28 Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep
29 Revenue FY18 5,575,407 #N/A
30 SPLY 6,926,773 #N/A



From January moving forward, I've written an IF formula to display "#N/A" as those months have not occurred yet. My goal is to extend a named range horizontally with each succeeding month so I do not have to change the cell references of my chart each reporting month. Any assistance is greatly appreciated.

Respectfully,

Michael

Welcome to the forum!

It's hard to be too certain I'm understanding your data arrangement without seeing a sample workbook, but dynamic named ranges are often done using OFFSET and one of the COUNT functions to craft the formula used to define the named range. Maybe something like the following for revenue (assumes numbers start in BK):

=OFFSET(BK29,0,0,1,COUNTIF(BK29:BV29,">0")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

For future reference:

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

My pleasure, and thanks for marking the thread solved. Good luck!