Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Last Text & Numeric Entry in Dynamic Range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #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
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      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

      Comment


      • #4
        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.
        Kris

        ExcelFox

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              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))
              Kris

              ExcelFox

              Comment


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

                Click Add then Ok.

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

                Comment


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

                  Kris and Dave thank you for you tips.

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X