Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Substituting a Name

    Excel 2003

    In my hunt for better performance, which is going well, I have come across a small issue which I cannot easily solve.

    I currently have a Workbook name CellAbove defined as =INDIRECT("R[-1]C",0)

    I use this in SUM and SUBTOTAL formulas to make sure that the cell above the formula is always where the total ends, regardless of what happens when rows are inserted or deleted.

    It works well, but I suspect that the volatile nature of the INDIRECT function is slowing things down - I have hundreds of instances of its use.

    I have found a potential substitute which seems to work (although I am not sure how !). I select cell A2 on Worksheet A, then enter CellAbove as a name and for its definition I type =!A1

    CellAbove now works as before in new formulas in any cell in any Worksheet BUT the substituted name does not take effect in existing formulas unless I go to edit each one and press Return.

    I can do this, but it will be a tediously long process and I was wondering if there is an easier way to refresh all existing formulas containing the CellAbove name ?

    Things I have tried which didn't work:

    1. In VBA, Application.CalculateFull
    2. In VBA, Application.CalculateFullRebuild
    3. Apply Names - but CellAbove doesn't even appear in the list !
    4. Giving the new function a different name and then doing a global Find and Replace following by recalculation
    Last edited by MartinM; 2016-04-07 at 04:54.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Martin

    Yes, I've come across that type of issue too. I'll have to look back at what I did to resolve it.
    Essentially, you want the formula to be 'volatile', but then that might put you back into the sluggish behaviour realm.
    I like the CellAbove method in principle, because, as you say, it deals nicely with some subtotalling requirements.
    Perhaps you could look into the possibility of using an off-sheet COUNTA formula to help determine certain (variable) row range 'end-points'

    zeddy

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Bah - I think it must be the new definition that's defective =!A1

    If I create a new name for it and change just one instance, all seems to be fine - until I close and re-open the Workbook.

    Then the formula with the new function doesn't calculate at all until I edit and enter it again.

    It all seemed so delightfully simple but I may have been too clever ?

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Sorry Zeddy - our posts crossed in the ether !

    There's nothing "wrong" with the present CellAbove function, in fact it is terrific - as you know I'm just experimenting with speeding things up.

    It isn't a biggie if the new function can't be made to work, I just don't like unsolved mysteries

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can use something like:
    =SUM(B1:INDEX(B:B,ROW()-1))

    You can do that as a defined name too, but it will be sheet specific.

    I never use the !A1 version because it can be flaky, especially when using VBA.

    Personally I tend to leave a blank cell between the data and the totals row and just make sure I insert above that, but I don't generally have to deal with other people using my workbooks.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Martin

    One of the biggest improvements might be to move away from Excel 2003, if you can.
    The Excel calc engines in the later versions are much more efficient.
    For example, you wouldn't want to reference an entire column in Excel2003 ,like = SUM(A:A) , whereas this is perfectly OK in later versions.

    zeddy

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I know that's sound advice and I am steeling myself to make what will, for me and my partner, be lots of work mostly in re-learning to get us back to roughly where we started ! Oh well.

    I hear that Office 2003 does actually work on Windows 10 but is not 100% functional and isn't "supported".

    If I read correctly, the general vote seems to be that Office 2010 was the best version after Office 2003 - we are heavy users of Word, Excel and Outlook, light users of Access.

    My biggest concern is around Excel though - is it an easy migration to Excel 2010 for spreadsheets heavy with Macros ?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Rory,

    I tried a UDF to SUM from a specified cell to the cell above but, unsurprisingly, it is slower than the CellAbove function which is neat and versatile.

    I think I'll leave things as they are, having satisfied myself that it's probably as good as it gets.

Posting Permissions

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