INDIRECT formula in chart for dynamic range

[rpinxt]

Say I have 1 chart for data of 1 month.
Now instead of making a workbook per month I was thinking about having a data tab for every month.

The format of the data would everytime be the same only the name of the sheet would refer to the month.
Then on a first sheet I would make a validation field on the 12 months of a year.

Now the trick would be to make the chart look at the data sheet that corresponds with month chosen in the validation field.
I know you can do this with an the INDIRECT formula but how would that be done in a graph range?

[Pepe Le Mokko]

I would not recommend creating one workbook per month but to keep all your data as centralized as possible, and from there create monthly tabs if you have to
Without seeing the layout, it is difficult to say but if you can use Pivot Tables and charts with slicers it make things very easy.
Perhaps post a small sheet ( see banner)

[rpinxt]

Well it would be only 12 excel books that you would have in 1 folder.
But yeah it would be better to have 1 workbook with 12 sheets and 1 sheet for the Graph.

Attached a sample.
So if you would set month to February the graph should look to the february tab (all other things like ranges are the same).

Not sure if working with pivots and slicers would fit as it is part of a bigger workbook and not to much can change.

[Pepe Le Mokko]

I would keep ALL the data on one sheet adding a column for the date and start from there

[rpinxt]

Yes you are right that would be the most simple.
However the workbook will have to be filled by other users and we need to have it as dummy proof as possible.
So therefore project team decided for data in 1 sheet per month, so that it is clear for everybody where to fill it.

[rpinxt]

Ok, so I guess it is just not possible to direct a 'chart data range' to different sheets.

If that is it, its it.
Then I will closed this thread later on.
Thanks for the responses.