Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Hi all,

    I am trying to define a named range with a dynamic range. I have used the ACOUNT and INDEX/LOOKUP methods, but I now want to use a method using my own (volatile) User Defined Function "GetEndRow" to determine the last row in the range.

    I can easily get the addresses of the start and end cells of my range using the following formulae:

    =ADDRESS(GetEndRow(),5)
    =ADDRESS(6,5)

    But I don't know how to put the two together to define the range i.e. i don't know the syntax/function to use.

    Can anyone help me define this named range?

    I thought it might be something like

    =RANGE(ADDRESS(6,5), ADDRESS(GetEndRow(),5)), but of course "RANGE" isn't a function so I get a #NAME

  2. #2
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    This gives the string

    =ADDRESS(6,5,3) & ":" & ADDRESS(GetWBSEndRowNum(),5,3)

    but if you put the result of this in a formula, does it behave like a reference to a cell or a just a string?

    for example, I have a formula :

    ={LOOKUP($E6:$E50,Rates!$B$4:$B$7,INDEX(Rates!$C$4 :$M$7,,MATCH(K3,Rates!$C$3:$M$3,0)))}

    However, if i replace the $E6:$E50 with a named range created using my address concatenation above, I get a #NA, presumably because the address concatenation is just a string.

    How can I convert that string "$E6:$E50" into a real cell range?

  3. #3
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    OK, I understand I can use the INDIRECT function to return the value in a cell given the string version of its reference. Can I use this somehow?

  4. #4
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Quote Originally Posted by dom_donald View Post
    OK, I understand I can use the INDIRECT function to return the value in a cell given the string version of its reference. Can I use this somehow?

    I think

    =INDIRECT(ADDRESS(6,5,3) & ":" & ADDRESS(GetWBSEndRowNum(),5,3))

    works

  5. #5
    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
    Or you can use the offset function:
    =OFFSET(E6,0,0,GetWBSEndRowNum()-5,1)

    Note the "-5" = - the start row +1 = -6 + 1 = -5

    The number of rows in the range is
    the max row - min row + 1 = GetWBSEndRowNum() - 6 + 1 = GetWBSEndRowNum() - 5

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    thanks very much for this - I figured that one out about 5 mins after I posted my topic and sent myself to the Dunce Corner for being so slow.


  7. #7
    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
    I'd still use what I suggested before:
    =$E$6:INDEX($E:$E,GetEndRow())
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Hi Rory, thanks for your help yesterday btw.

    Your suggestion is indeed better - it doesn't rely on hardcoded numbers and is quicker.

Posting Permissions

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