Edit formula in Chart Data Range to make it dynamic

Hello Excel friends,

I am using Excel 2016 on my little Dell / Windows10. I have been given instructions on where to go to create a dynamic table. In this case, I don't want to entirely recreate the spreadsheet (although I will try to learn and use that method in the future) when my purpose will be served if someone can help me EDIT THIS FORMULA to retrieve the row number from another sheet.

I have the following formula in the "Chart data range:" of the Select Data Source of a chart, (once each in 45 different charts) that is on sheet '1-1-6'.

Please Login or Register  to view this content.

It works fine, except, every month I have to manually change the row number for every chart one-by-one. My data for the chart is on sheet '1-1-6D' as shown in the formula.
I could either replace the row number (12 in this case) with the following code that displays the row number or place the code in cell '1-1-6D'!$F$10 and edit the formula above to retrieve the row number from '1-1-6D'.

Please Login or Register  to view this content.

The problem here is that I cannot figure out how to do what I stated above. I don't have a preference which method is used, as long as it works.

Any assistance from y'all would be greatly appreciated. Thanks in advance!

It sounds like you are looking for "dynamic chart ranges". The easiest is usually to make your chart's source data part of a structured table. Structured tables "know" how big they are and chart's that reference the table will usually expand/shrink as the table expands/shrinks. The other approach is to create dynamic named ranges for your chart data that will grow/shrink as your data grows/shrinks. More detail here: https://chandoo.org/wp/dynamic-chart-data-series/ or put "excel dynamic chart" into your favorite internet search engine.