Solved

Dynamic range in Excel 2010- how to set it up

Posted on 2013-12-20
6
470 Views
Last Modified: 2013-12-26
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
Comment
Question by:ssmith94015
  • 2
  • 2
  • 2
6 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39732050
Try:

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

this assumes nothing in column A above row 5
0
 

Author Comment

by:ssmith94015
ID: 39732145
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
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 250 total points
ID: 39732190
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 46

Accepted Solution

by:
Martin Liss earned 250 total points
ID: 39732430
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 Closing Comment

by:ssmith94015
ID: 39740348
They both worked Nd the explanation helped.

Sandra
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39740357
I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do ASP.NET and MVC work together? 4 25
location range 4 22
Configure Sharepoint 2013 to allow Excel files to be edited online 9 50
Search for a value in Column? 5 20
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now