Dynamic Named Ranges

  • I am trying to name ranges in a spreadsheet that spans 2 columns (A and B). The data contained is a text values in cell A and numberic values in B. There are 15 sections of data separated by a blank line. I want to use the offset function to name my ranges based off of:


    1) The header of the section (Anchor Cell)
    eg: if cell a# = Fullname this is anchor


    2) Select cells from the cell after the anchor cell down until the next zero value in cell B


    I eventually want to make this a VB macro. I want to be able to click a button to search for the headers and name the ranges accordingly.


    Here is my named range I created, but it goes all the way down to the end of the spreadsheet instead of stopping at the first zero value in col B


    =OFFSET(A!$A$2:$B$2,0,0,COUNT(A!$B:$B))


    All help is greatly appreciated!

  • Im really bad with lots of words, i think understand what u are getting at - i have something similar - any chance of an example of the spreadsheet?


    I think what u are getting at is that your anchor cell is variable?

  • A complex formula could be constructed for each range, but if you are going to use a macro to define the names anyway, why not avoid the pain of constructing the formulas and just let your macro simply cycle through defining the ranges as it comes to them? What needs to be dynamic?

  • Here is an example of a complex formula for a defined name. It assumes no more than 500 rows to a name (change the 500 as needed) and that the blank cell delimiters in column A have been replaced by a single space (key assumption).
    =OFFSET(A1,MATCH("fullname",A:A,0),0,MATCH(" ",OFFSET(A1,MATCH("fullname",A:A,0),0,500,1),0)-1,2)

  • I would love to be able to just use a vb macro to name the ranges. I thought that doing it in Excel would help me write the macro. I don't write them very often. This spreadsheet is created every week and the ranges will be different. I want to name the ranges so I can insert them into an Excel template. If there is a good way to bypass the formula part and get right to the macro, I would like to hear your idea in a little more detail and try it that way.



    Here is an example of what it looks like:


    STATE FREQUENCY
    CA 334781

    ZIP FREQUENCY
    9 334781

    SCOREDTE FREQUENCY
    20040301 334781

    MAILDTE FREQUENCY
    20040422 334781

    PRINTKEY FREQUENCY
    126TW19 167390
    146TW19 167391

  • Try the following macro. It works on your example.

  • Thank you so much.


    I am now trying to paste those ranges into a template. I thought I could just say it equals the range on another spreadsheet, but it is only putting in one value.


    How can I insert a range into A1 and B1? I need the cells the move down for the first insertion, then just paste into the next cells. Here is the format:


    Fname Freq Fullname Freq StName Freq
    --------------- ----------------- ---------------
    a---------123 a---------- 258 a---------236
    b---------321 b-----------852 b---------632



    Thanks again

  • Since the name covers more than one cell you will need to enter it as an array. Select the cells where you want the data for a name to appear, then type the reference and hold down the control and shift keys when you hit the Enter key. One problem may be that you won't know how many rows your name needs. Or you could modify the macro to write the appropriate cell reference to your template rather than use defined names. That might be a better solution.

Participate now!

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