• I have the following formula on top of my data in cell A10: SUBTOTAL(9;Sheet1!$A$15:$A$65500)
    the problem is that every time i erase the last line or the data from the last line the formula changes too!from 65500 it goes to 65499...65498 and so on......WHY?
    P.S. I'm using a great range...up to 65500 so that i don't have to modify it every now and then.
    :puzzled::question::puzzled:

  • Instead of $A$15:$A$65500 could you use $a:$a in your specific application. It will pick up the entire column. If I'm importing & using long variable-length ranges I try to layout the spreadsheet so that I can use sum($a:$a), sumproduct($a:$a,1,$b:$b) etc... (not very memory efficient though...).

  • Hi teoant


    Sounds like you are deleting the last row? If so, a much better option is to simply Edit>Clear all and sort if needed.


    As Egad has pointed out, using an entire column in a formula is not very efficient. I would suggest learning about Dynamic Named Range Once you get the hang of them, you will wonder how you lived without them :)

  • I wonder how *anyone* lives without dynamic named ranges !!


    I agree with Dave, they're well worth the investment in the long run


    this'll also work, although it will need to be augmented if you want to copy the logic somewhere else :


    =SUBTOTAL(9,INDIRECT("Sheet1!$A$15:$A$65500"))


    INDIRECT brings back the value of the reference in text within the quotes..... since, here, the ref is a range, it will treat it as a range. However, as it's text, it will not "relative" itself when rows are deleted, unlike $absolute$references


    if you do need to copy it over some columns, post back and we can augment it to do this in one go


    long term though, dynamic named ranges will suit you much better

  • THANK YOU VERY MUCH FOR YOUR RESPONCES GUYS!
    I WILL TRY TO WORK SOMETHING OUT USING DYNAMIC RANGES...AS SOON AS LEARN MORE ABOUT THEM!
    BY THE WAY...HOW COULD ONE FIND OUT SOME MORE INFORMATION ABOUT USING DYNAMIC RANGES???