Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Last Text & Numeric Entry in Dynamic Range

1. Established Member
Join Date
18th January 2006
Posts
524

## Last Text & Numeric Entry in Dynamic Range

I know this easy question but I tried learning about dynamic ranges but I am stuck on two problems. I can't get last text and last number in two different worksheets. Please refer to attachment.

Biz

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Dynamic range- last text,last numeric

Biz,

Last Numeric:
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))

Last Text:
=INDEX(A:A,MATCH(REPT("Z",255),A:A))

Last Either:
=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),A1:A65535)

HTH

3. Established Member
Join Date
18th January 2006
Posts
524

## Re: Dynamic range- last text,last numeric

Originally Posted by Reafidy
Biz,

Last Numeric:
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))

Last Text:
=INDEX(A:A,MATCH(REPT("Z",255),A:A))

Last Either:
=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),A1:A65535)

HTH
Hi Reafidy,

Thanks for answer by I am trying use offset in Dynamic range to get the answer. Do you know how to fix the problem I have?

Biz

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Dynamic range- last text,last numeric

Originally Posted by Biz
Hi Reafidy,

Thanks for answer by I am trying use offset in Dynamic range to get the answer. Do you know how to fix the problem I have?

Biz
Offset is a volatile function. So better to avoid wherever possible.

5. Established Member
Join Date
18th January 2006
Posts
524

## Re: Dynamic range- last text,last numeric

Originally Posted by Krishnakumar
Offset is a volatile function. So better to avoid wherever possible.
Hi Krishnakumar,

Welcome back Krishnakumar!

I agree with u but I am trying to learn about Dynamic ranges. I am find ing it difficult to make offset work to find last text or last number in a column.

Biz

Excel Video Tutorials / Excel Dashboards Reports

6. Established Member
Join Date
18th January 2006
Posts
524

## Re: Dynamic range- last text,last numeric

Hi,

I found another formula that could do the job.

In "Last Text" worksheet
=OFFSET('Last Text'!A1, COUNTA('Last Text'!A:A)-1,0)

In "Last Numeric" worksheet
=OFFSET('Last Numeric'!A1, COUNTA('Last Numeric'!A:A)-1,0)

Biz

Excel Video Tutorials / Excel Dashboards Reports

7. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Dynamic range- last text,last numeric

Both the formulas will fail if there is any blank cells inthe range.

Try,

DynaRange =Sheet1!\$A\$1:INDEX(Sheet1!\$A:\$A,LOOKUP(9.9999999E+307,CHOOSE({1,2,3},MATCH(9.9999999E+307,Sheet1!\$A:\$A),MATCH("zzzzzzzz",Sheet1!\$A:\$A),MAX(MATCH(9.9999999E+307,Sheet1!\$A:\$A),MATCH("zzzzzzzz",Sheet1!\$A:\$A)))))

DynaRangeNum =Sheet1!\$A\$1:INDEX(Sheet1!\$A:\$A,MATCH(9.9999999E+307,Sheet1!\$A:\$A))

DynaRangeTxt =Sheet1!\$A\$1:INDEX(Sheet1!\$A:\$A,MATCH("zzzzzzzzz",Sheet1!\$A:\$A))

8. ## Re: Dynamic range- last text,last numeric

The way I do this is as below and based on Column "A" being the dynamic range with blanks, numbers and text;

Go to Insert>Name>Define and use the name MaxRow and have it Refer to: =MAX(IF(ISNA(MATCH(-1E+306,\$A:\$A,-1)),0,MATCH(-1E+306,\$A:\$A,-1)),IF(ISNA(MATCH("*",\$A:\$A,-1)),0,MATCH("*",\$A:\$A,-1)))

Now simply use the named variable MaxRow inside any dynamic type named range as the row expand component.

9. Established Member
Join Date
18th January 2006
Posts
524

## Re: Last Text & Numeric Entry in Dynamic Range

Kris and Dave thank you for you tips.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno