?
Solved

Dynamic named range formula

Posted on 2013-06-04
2
Medium Priority
?
318 Views
Last Modified: 2013-06-04
Hi,

I always have data alike this

HEADING
DATAA
DATAB
DATAC
DATAD
DATAE

0

Open in new window


So I always have data ending with a zero 1 row below the last data. It could be 100s of lines.

Eg: I could have data from

A2 to A982

and then have a zero (0) in line A984

What I want to do is create a dynamic named range that will grab just the data part and omit the zero and the space above it based on formula only (currently I am doing it via VBA and want to eliminate vba)

Thank you in advance!
0
Comment
Question by:Shanan212
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 2000 total points
ID: 39220347
Try something like:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(0,Sheet1!$A:$A,0)-2)

assuming the sheetname is Sheet1
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 39220361
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(0,Sheet1!$A:$A,0)-1)

^ That worked; instead of -2, I changed it to -1

Thanks!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

601 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