Share via

Excel Dynamic Range with Non Continuous Columns

Anonymous
2016-02-12T13:54:27+00:00

I know I can dynamically define the range A2:D7 using the formula 

=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A),4).  The number of rows in column A can grow, thus the need for a dynamically named range.

How can I get the same thing, but for the non-continuous range A2:A7,E2:G7.  I need column A as my "anchor"

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Vijay A. Verma 104.8K Reputation points Volunteer Moderator
2016-02-12T19:20:46+00:00

Try using INDEX rather than OFFSET to avoid volatility..I am giving just one way to create a dynamic range...This uses A1 as anchor.

INDEX(A:A,2):INDEX(A:A,COUNTA(Sheet1!$A:$A)),INDEX(A:Z,2,5):INDEX(A:Z,COUNTA(Sheet1!$A:$A),7)

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2016-02-12T23:57:39+00:00

    Hi,

    To work with ever expanding ranges, convert your range into a Table.  Any formula referring to your data range, will automatically expand in size.

    Was this answer helpful?

    0 comments