Improve company productivity with a Business Account.Sign Up

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

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
Asked:
ssmith94015
  • 2
  • 2
  • 2
2 Solutions
 
NBVCCommented:
Try:

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

this assumes nothing in column A above row 5
0
 
ssmith94015Author 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
 
NBVCCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Martin LissOlder 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
 
ssmith94015Author Commented:
They both worked Nd the explanation helped.

Sandra
0
 
Martin LissOlder 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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now