Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Last Text & Numeric Entry in Dynamic Range

  1. #1
    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,271

    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. #3
    Join Date
    18th January 2006
    Posts
    524

    Re: Dynamic range- last text,last numeric

    Quote 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. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Dynamic range- last text,last numeric

    Quote 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. #5
    Join Date
    18th January 2006
    Posts
    524

    Re: Dynamic range- last text,last numeric

    Quote 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. #6
    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. #7
    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. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    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)))

    Click Add then Ok.

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

  9. #9
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 4
    Last Post: March 25th, 2008, 07:12
  2. Locate Last Numeric Entry Of Every Third Column
    By dcm in forum Excel General
    Replies: 5
    Last Post: September 20th, 2007, 08:42
  3. Automatically Selecting Dynamic Fields And Requiring Text Entry
    By TestPlansRule in forum Excel General
    Replies: 9
    Last Post: February 24th, 2007, 09:16
  4. Restricting Textboxes to numeric entry
    By kingsba in forum Excel General
    Replies: 2
    Last Post: October 31st, 2005, 20:57

Bookmarks

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