Unable to use a dynamic named range as a data series - what am I doing wrong?

I've got some variable data I want to plot on the x axis of a scatter chart, so did some formula magic to create a column with no intervening empty cells between the data to be plotted (since empty cells are apparently a no no on the x axis).

I was able to create a dynamic range that adjusts automatically to the length of the column as data gets added or subtracted. Works great. In case it's relevant to the answer, first I concatenate some text to create the range I want to look at =CONCAT("G2:g",VALUETOTEXT(COUNT(I2:I30)+1), then I use the Indirect function with the results to dynamically represent the data I want to plot =INDIRECT(Test!$G$33).

I can type the range name in a cell and it displays as expected, as well as grows/shrinks based on the underlying data.

Problem is when I try to type the range name in as the data series source, I'm getting an error. I've done multiple searches all saying this works with exactly the same method in all articles. In the data series, I'm just typing the following in my "Series X Values": =Test!AdvInf ; where Test is the worksheet tab, and AdvInf is the named range.

When I try to save I get the error message basically telling me to check everything because something isn't correct. So i'm guessing either I have incorrect syntax or regardless what the Internet says, I can't do this.

Just looking to understand if there's a way to accomplish this. This is the last step of a very long charting journey!

Thank you!

INDEX is preferable

Unless you want to lock the references for some reason,

Excel does not adjust text values when you insert rows or otherwise move the array formula.

using INDEX (as P.b mentioned) is preferable, here's an example with absolute reference:

=Sheet1!$G$2:INDEX(Sheet1!$G:$G,COUNT(Sheet1!$I$2:$I$30)+1)

Enter name as Workbook/Sheet

INDIRECT

=INDIRECT("Sheet1!$G$2:$G$" & COUNTA(INDIRECT("Sheet1!I2:I20")) + 1)