Select chart data based on last cell with a value >""

Hi All,

Long time reader, first time poster. Thank you kindly for all of the help the forum has given me so far.

I have a database of sales information where the user can select to report; monthly, quarterly or annually in a summary table. I am currently using one summary table to report on each basis (month,qtr,year) whereby the dates range in the column headings are calculated using an if/eomonth function up to a specified end date and the following cells in the row are blank.

As the cells after the end date are not blank (the if function is just returning a "" value if the maximum date in the preceding cells is equal to the specified maximum date) the number of columns that are populated doesn't change, however, if the user selects to report in years, I need to change the range of the charts to avoid returning "" values in the x-axis.

Therefore I have two questions;

firstly, is it poor form to try and report on all three bases in the one table - should I calculate them all in a separate sheet and call the appropriate table to the summary page?

secondly, how do I change the data range via VBA based on the time basis of reporting (noting that the start date and end date are both variable)?

I have been playing around with a loop and offset function that does select the range that I want, however, how do I then update the chart to reflect the selected range?

Thanks in advance,

Peter

hi Peter, do you mean sort of that? Change date in C5 or C6

Thanks Watersev, perfect.

Hi again Watersev,

Now that I've moved the chart to another sheet in the workbook, I am getting a subscript out of range error in the part of the code that refers to the chart object and the source data i.e.

Please Login or Register  to view this content.

is there a quick way around this?

Thanks again,

Peter