Solved

Dynamic named range formula

Posted on 2013-06-04
2
291 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 500 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

803 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