I have a worksheet that contains a macro to add a new row (with data pulled from other places of the workbook; not included in this example workbook) at line 62. There are charts above this data, and I want the chart to always graph data from rows 62-71.
I've tried using named ranges with the indirect function (I'd have column letter and numbers 62 and 71 typed in different cells and use something like =INDIRECT(Report!U8&Report!U9&":"&Report!Y8&Report!Y10) as the "Refers to" in the Name Manager. This formula always messes up for some reason after I add the row (e.g. U8 will change to S8 or something of the sort).
I've tried using the index function instead of the indirect function in the name manager; for example, =Index(B62:B71,1):INDEX(B62:B71,10), but when I add rows with the macro button, the formula in the name manager changes as well.
What can I do to make the chart range stay the same when I add rows with the macro button?
Bookmarks