Manual Y axis value based on cell content and restrict chart data to what is available

[analystbank]

Hello Seniors / Experts

Sample Excel Workbook in 1997-2003 version is attached for reference.

Cell H1 has the Max (latest) date upto which I am retrieving / pulling data using formulas in Range Q3:X32, starting from Q3 and upto Q32. Data will not go beyond Row 32.

Similarly second set of data for identical date series, is retrieved / pulled using formulas and kept in range Z3:AF32. Column Q is date and common for both data table.

Please note that since H1 is the max date, data is restricted upto that date only, This will keep improving day by day, and max it could fill upto cell Q32 beyond that there won't be data.

For Data Table in Range Q3:X32
manually i want to set the boundaries for Chart Scale purpose - Y-AXIS.
Cell Q59, i am using Max formula to say that data is only upto date mentioned in Cell H1.
Cell T59, has max value plus 10%
CellU59, has Min value minus 10%, but minimum 0.05

likewise for data series 2 , table 2, in range Z3:AF32 are filled accordingly in Cell AB59 (Max value), and Cell AC59 (Min Value).

What i want to achieve

I want to have chart (sample chart type/class given, which should not be changed for obvious purpose) which should have

1) Axis Y (Vertical line) to have Minimum and maximum value based on values given in Q59, T59, for Table / Chart 1 and U59 for Data/Table 2, Cell AB59 (Max value), Cell AC59 (Min Value).

2) For both data table/chart, Date should only be upto what is mentioned in Cell H1 or Q59, same derived from H1 upto which a given point of time, I have data, which will increase.

So, whenever date progresses, day by day, data table will be updated and filled progressively, but max upto Cell Q32, Chart data should be only upto the latest date. In current scenario, since entire range is taken as data source,even the blank cells are filled as zero on chart X axis, which should not be the case and data range for chart be restricted for latest date having data.

Sorry, If I am poor in explaining what exactly i want, but still would narrate as

a) Chart data selection should only be upto last specified date available, and
b) Y axis Scale should be derived from values given in Cell T59 (max), U59 (Min) and similarly for chart 2 in cell AB59, AC59

Please note that, in a given worksheet i may have many such multiple chart tables, and so does the chart, which later i copy to dashboard to another worksheet .

Besides, these data table values are derived from multiple worksheet using several formulas and would be dynamic, like if i change key input , all data will change in all respective tables.

I dont know how to use named range, offset or vba combination to achieve the result.

Thanks,

[MrShorty]

Try this. https://peltiertech.com/link-excel-c...lues-in-cells/ Peltier shows several different steps/stages of development, culminating in an event procedure that will automatically update the axis parameters based on values entered into cells. There will probably be additional development for your exact requirements, but such edits should be straightforward, assuming you have understood what he talks about in his essay.

[analystbank]

thanks