1. 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:

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. This gives the string

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. 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. Originally Posted by dom_donald
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

works

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

8. 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
•