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?