CODEWITHSUNDEEP
excelvba
ale.tenorio
ale.tenorio

Reputation: 41

Reference a range to a cell value

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.

Upvotes: 0

Views: 626

Answers (2)

iDevlop
iDevlop

Reputation: 25272

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

Upvotes: 3

John Joseph
John Joseph

Reputation: 1187

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.

Upvotes: 1

Related Questions