Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    function (Excel)

    Does anybody know of a way of working this out ??? If i have a colum in excel that i keep adding new entrys in i.e. 100 in A1 123 in A2 etc and the list of entries can go on and on say down to A500. The problem i face is that i use the A500 ref in another calc so every time i enter a new entry in say A501 i have to amend the calc to point at it....argh!!!!!! any ideas guys n gals

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: function (Excel)

    Welcome to Woody's Lounge!

    If you want to refer to the last filled cell in column A (assuming that all cells above are filled too), you can use OFFSET($A$1,COUNTA($A:$A)-1)
    If you want to refer to the range from A1 up to and including the last filled cell in column A (assuming that all cells in between are filled too), you can use OFFSET($A$1,0,0,COUNTA($A:$A))

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function (Excel)

    <P ID="edit" class=small>(Edited by sdckapr on 07-Feb-05 15:15. Added PS)</P>Are you always after the last value (number) in column A?

    <pre>=VLOOKUP(9.99999999999999E+307,A:A,1)</pre>


    If you want the last text string try:

    <pre>=VLOOKUP(REPT("z",255),A:A,1)</pre>


    If you want text or numbers you could use an ARRAY (confirm with ctrl-shift-enter)
    <pre>=INDEX(A1:A1000,MAX(IF(ISBLANK(A1:A1000),0,RO W(A1:A1000))))</pre>


    Expand the range as desired. Note that the array can not be the entire column A1:A65535 or A2:A65536 is the limit.

    Steve
    PS. This works differently than Hans' formula. It will work even if the column has blank cells within the data.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: function (Excel)

    Additionally another for combined text/numbers you could use:
    <pre>=INDEX(A:A,MAX(MATCH(REPT("Z",255),A:A),MATCH (9.99999999999999E+307,A:A)))</pre>


    This requires that there be both text and numbers, not either.

    Steve

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: function (Excel)

    Thanks guys n gals you make it seem so simple
    VERY much appreciated
    Ian

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: function (Excel)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>...so whos the gal <img src=/S/question.gif border=0 alt=question width=15 height=15>... <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    (just a side jogger)...ha ha!
    Regards,
    Rudi

Posting Permissions

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