Dynamic Name Range not working with indirect formula

Hi,

I am trying to figure out if it is possible to use a dynamic name range in an indirect formula, I can't get it to work. I think the terms I use to explain might not be the correct terms, so I'll try to explain as detailed as I can, and I'll add an example to illustrate my question.

A named range can be defined by selecting the table with data. For instance a list of customer numbers and names, then giving it a name in the white box in the upper left corner and pressing enter. In my example, I selected A1:B7 and named it StaticNameRange

However, I want it to be more dynamic, so that it selects all cells, even if I add more customers later. I know I can use A:B, but that has it's limitations, and I want to learn more about named ranges. So I use a formula to define the range:

=Offset($A$1,0,0,COUNTA(A:A),2)

This works fine for ordinary formulas. I posted in my example that I use a VLOOKUP on the named range to search for a customer number and return the name and it returned the results fine. But When I put the name of the range in H2, and use Indirect(H2) instead of the name of the range, it returns #REF.

When defining the name of the range in the first way I mentioned, it works fine to use Indirect, this is only a problem when using the offset formula to define the range.

So my question is: How can I use an dynamic Name range in an indirect formula? Maybe there is another way to make a dynamic name range that will work?

Best Regards
Tbez

INDIRECT "does not like" dinamic named ranges

I hear you tim201110.

Indirect can be quite picky when it comes to what it likes and doesn't like. I found out the other day that i doesnt like ()-signs in sheet names either..

Maybe it is best to just use =A1&"B:"&COUNTA(A:A) to keep indirect happy

Does anyone have a suggestion for a dynamic name range that will work with indirect?

http://chandoo.org/forum/threads/pas...54/#post-32423
http://www.ozgrid.com/VBA/range-name-address.htm

Hi,

Thank you for helping Tim and Haseeb. Indirect didnt work with dynamic name ranges, but with the help of a short VBA, it worked afterall.

Tim posted a link to a post by SirJB7, and I'll explain how it worked in this post.
I inserted the VBA-code below in a new module.

Please Login or Register  to view this content.

The code adds a function called RetrieveRangeForName

So from my original formula, I added the new function:

Before: =VLOOKUP(I2,INDIRECT(H2),2,False)
After: =VLOOKUP(I2,INDIRECT(RetrieveRangeForName(H2)),2,FALSE)

And voila, dynamically named ranges work with indirect!

Best regards Tbez.

PS: My attachement is a macro. if you want to test this withoout downloading a macro, you can use the first attachement I posted, and paste the code to try the same with no risk.

Hi there,

I had a weird situation where an example spreadsheet that I downloaded from the web to show how to use a dynamic named range drop-down worked 100% fine but when I tried to do it on my spreadsheet, it just refused with the message that it evaluates to an error!

My spreadsheet had a sheet called "Personal Finance" and "Business Finance". In my table, I had a field called category with a drop down for either "Personal" or "Business". When I chose one of these, I wanted the next field to have a drop down that was specific to either personal or business finance items.

My solution was to write in the dependent data validation field the following formula:
=INDIRECT("'" & A2 & " Finance'!$A$2:$A$20"). So this had the effect of giving me a dynamic drop down list in column B that was dependant on the value in column A.

Interestingly, if I only have say 4 values in my Personal Finance list, and my range says A2:A20, it only shows the 4 values so Excel has the intelligence to filter out the blank values. I could have used a counta function I suppose but that makes the formula long and clunky and this worked so no need.