Using INDIRECT with dynamically named ranges

[Frank Kabel]

Hi
could you post some more details about your
function 'UseThisRange()' e.g. the actual code you use.
You may also then post the actual formula you have used

-----Original Message-----
I have a bunch of dynamically named ranges that I want to

perform lookups from i.e. my named range contains a
formula such as offset($A$2,0,0,MATCH("*",$A:$A,-1)) that
returns a range that automatically grows as I add data

I also have a formula that tells me the name of the range

I want to use based on certain conditions I'll call
it "UseThisRange()".

I want to put this formula in an INDIRECT function so

that it returns the correct range.

Finally I want to take all of this and use it as the range in my lookup function.

The problem is that the INDIRECT is returning a #REF

error when I pass it the name of any of my dynamic
ranges.

 

[Frank Kabel]

Hi
as Aladin already stated this does not work with
dynamically created range names using OFFSET for example.
so one workaround would be to used fix defined names
without a dynamic range

-----Original Message-----
What I refer to as the UseThisRange() function is no more

than a named reference to another cell which has the name
of the range in it concatinated with "_data". How that
cell gets the name in it is kind of complicated, wich is
why I left it out. However it could be any function that
returns the name of the range since even INDIRECT
("RangeName"), returns the #REF error.

The actual final function including the lookup and everything is:
VLOOKUP($A7,INDIRECT(PrevUnrealizedMonth&"_data"), UR_col - ID_col + 1, false)

Where:
- I have 12 dynamic ranges

named "Dec_data", "Jan_data",...,"Nov_data"

- PrevUnrealizedMonth is the name for a cell that

contains one of "Dec", "Jan",...,"Nov"

- UR_col is the named formula COLUMN($E$1), the value should come from column "E"
- ID_col is the named formula COLUMN($B$1), column "B" is the first column in the range

As I said, the uderlying problem is that INDIRECT

("RangeName") returns #REF when RangeName refers to a
dynamic range as I defined it the original post.