x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 574

# Dynamic range in Excel 2010- how to set it up

Normally I could do this, but am so tired brain is not working.  I have data in a worksheettaht starts at cell A5 and goes to column D.  I am trying to create a dynamic named ranged,but simply am too tiredto analyze the statement

=OFFSET(\$A\$5,0,0,COUNTA(\$A:\$A),1)

To figure out how to get it to cover columns A:D, start at row 5 and then capture any changes the user makes.

Sandra
0
ssmith94015
• 2
• 2
• 2
2 Solutions

Commented:
Try:

=OFFSET(Sheet1!\$A\$5,0,0,COUNTA(Sheet1!\$A:\$A),4)

this assumes nothing in column A above row 5
0

Author Commented:
Yes, there is data in rows 1 to 4 which is why I need I to start at row 5.  Right now, it is not working.
0

Commented:
Then perhaps:

=OFFSET(Sheet1!\$A\$5,0,0,COUNTA(Sheet1!\$A:\$A)-4,4)

or if some of the A1:A4 cells are filled,

=OFFSET(Sheet1!\$A\$5,0,0,COUNTA(Sheet1!\$A:\$A)-COUNTA(Sheet1!\$A\$1:\$A\$4),4)
0

Older than dirtCommented:
Here's an example formula where the first row is skipped and an explanation of the settings.

=OFFSET('Sheet Name'!\$A\$2,0,0,COUNTA('Sheet Name'!\$A:\$A)-1,1)
(the A\$2 is the first cell in the range)

Legend
•      'Sheet Name'!\$A\$2 - The referenced cell.
•      0 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
•      0 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
•      COUNTA('Sheet Name'!\$A:\$A)-1- (Optional.) Indicates how many rows of data to return. This number must be a positive number.
•      1  - (Optional.) Indicates how many columns of data to return. This number must be a positive number.
0

Author Commented:
They both worked Nd the explanation helped.

Sandra
0

Older than dirtCommented:
I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.