Cell value as named ranged reference

[sdrubezki]

Hello, I'm trying to use a cell value as reference for a dynamic range in a vlookup formula. I tried indirect, concatenate, etc and it's not working. I know the def of the dynamic range is ok since when I use the offset formula in the vlookup it works. Any ideas? Thanks.

 

[konew1]

Indirect should work. Post an example of your data.

 

[sdrubezki]

This is the idea:
In Sheet1 I have a reservation form. User enters city in cell B11 and list of sites appear in cell B12 (dependent list, data in sheet2).
What I'm tryingh to do is to retrieve contact details for that specific site. For each I created a dynamic ranged named "CityNameDynamic". Since this is not the what appears in the drop down, I used "substitute(B11, " ","")&Dynamic"".
This didn't work, I thought since it's a formula, so I added a macro to copy/paste special only text. Teh vlookup should take this name as the range name but it doesn't!
Any ideas?

 

[konew1]

If B11 has a city, what is the space character you refer to in
substitute(B11, " ","")&Dynamic"".
If B11 has the city isn't the requirement to be B11&"dynamic"

 

[sdrubezki]

Since there're some cities with 2 names (e.g. New York and I named the range NewYorkDynamic).

 

[sdrubezki]

I've been reading a bit and I apparently indirect "doesn't like" dynamic ranges. Is that so? If yes, any idea how to overcome this?