dependent lists

[neveu]

Hello forum,

i've created several named ranges in sheet "Sheet1" starting with A1 and B1
(i have like 10 defined named ranges but for example i only copied these 2)

5A 5B
text1 txt1
text2 txt2
text3 txt3
text4 txt4
text5
text6

the first range include all the values below 5A (it's defined range name in BND5A)
similar for the column B range

what i want to do is to define a drop down list where when i have in the cell next to it the value 5A to show me the values in the BND5A ranges).

I've been struggling with this but can't figure it out how to solve it.

any inputs are very appreciated.

thank you
I've read that you can use the Indirect function to get

 

[Firefly2012]

Hi

Check this link out:

http://www.contextures.com/xlDataVal02.html

 

[neveu]

Hi Firefly,
thank you for your input. unfortunatly this doesn't solve the issue
as the main challenge in my example is that the Name of the ranges could not the exactly the same as they start with a number ( which is not allowed in a named range - unfortunatly)
if i put some text before it "Bnd" for example then there will be no connection between the values in the cell ( ex 5A, 5B) and the named ranges Bnd5A, Bnd5B)

the thing is that the values in the cell cannot be changes

thank you

 

[Firefly2012]

But you would just set your Validation list (assuming it is in cell B1 and you have 5A in A1) to be based on:

=INDIRECT("BND" & A1)

 

[neveu]

hi Firefly,

i've just tried that but it won't work because by doing the concatenation the Indirect function argument is a text "Bnd5A".

is there any way to convert this to the Range Bnd5A?

 

[Firefly2012]

Hi

That's what INDIRECT does (takes a string and translates to a range!). Did you try it? How is BND5A defined? It must be a static named range (you won't get it to work if BND5A is a Dynamic Named Range) - if this is the case you will need to rethink what you are trying to do.

 

[neveu]

indeed Bnd5A is defined as a dinamic range.it would not work otherwise in my case as the ranges arhave diferent lenghts every time

 

[Sandeep Warrier]

You can try this workaround.

Function dRangeAddress(dRange As String) As String 
    dRangeAddress = Range(dRange).Address 
End Function

Source: http://www.ozgrid.com/forum/showthread.php?t=39429

Suppose you have 5B in cell A1, and you need your validation list consisting of values in range Bnd5A in cell B1, in some other cell (which will never be used, IV500, for example) use the formula =dRangeAddress("Bnd"&A1)

In your validation list, use =INDIRECT(IV500)

 

[Firefly2012]

Nice suggestion Sandeep

 

[neveu]

thank you very much Sandeep,
I have 1 question: does the Name ranges have to be in the same spreadsheet or they could be in separate one.
from what i see, the functions returns the address, so no reference is made to the spreadsheet.

still, it would be niceto have the ranges in a separate sheet.
do you have any suggestions/ helpful tips on this one?

thank you in advance