Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Dynamic named range formula

Posted on 2013-06-04
Medium Priority
310 Views
Hi,

I always have data alike this

``````HEADING
DATAA
DATAB
DATAC
DATAE

0
``````

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)

0
Question by:Shanan212
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Accepted Solution

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

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

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.