Maintain Dynamic Range References After Row Insert

    From Ozgrid I picked up a formula that creates dynamic named ranges. Here is the formula I'm using for my needs:


    =OFFSET(CATEGORIES!$B$15,0,0,COUNTA(CATEGORIES!$B$15:$B$65536),1)


    My dynamic range begins at B15. There will be many times when a row will need to be inserted at row 15, however, when this happens the formula above changes to $B$16, then $B$17, and so on. I'm confused because I thought the formula was absolute based.


    Is there a way to make it stay at B15, or make it step back every time a new row is inserted, or start at B14 (the heading) but exclude the heading from the results of the dynamic range?


    Thank you.

    Re: Maintain Dynamic Range After Row Insert


    I think perhaps you need to review the process you're using, inparticular why you're INSERTING rows (which would be a perfectly good approach for stretching an otherwise fixed/static named range).


    A Dynamic named range works best when you're adding items to the list of values (ie filling in another cell). This doesn't involve or require the insertion of rows.

    Robert Hind
    Perth - Western Australia

    Re: Maintain Dynamic Range After Row Insert


    The user needs to see the data they just entered, which is why the data is going to the top of the list. There's a whole reason for this that isn't worth explaining here - just that this is how the program is designed and there is a madness to that design.


    +++++++++++++++++++++++++++++++


    Let me explain better. This dynamic range question works with a lot of vba code. There is a list of categories, and the user can add/edit/delete any of these. If the user adds a new category, it is added to the top of the list (this is a requirement). On another sheet I have a few validation cells which use dropdown boxes to view the category list (this is why I need the named range, because it is on another sheet).

    Re: Maintain Dynamic Range References After Row Insert


    That appears to have done the job, thank you Dave. Excellent tip.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login