A better way to put it:

1. I have many dataframes of variable column length, so xmax_pos = col_length.
2. I want to apply a formula using this col_length as a range for each dataframe; if my range is bigger than my df, the formula won't work.

=formula(A1:A"xmax_pos")

Is there a way for me to assign a range indirectly? Without manually modifying the range.

CodePudding user response：

You can also create a dynamic named range:

=$A$2:INDEX($A:$A,COUNTA($A:$A)))

See https://exceljet.net/formula/dynamic-named-range-with-index
As @BigBen suggested, it's better than using OFFSET

CodePudding user response：

Yes, and you are quite close. Simply build a string and use that in an "indirect" function.

For example:

=indirect("A1") 

...will return the value in cell A1.

In your specific case, you would build the range reference like: "A1:A" & xmax_pos

I can't be more specific than that due to lack of specifics in your question.