INDIRECT and drop down menus

[stojko89]

In the attached file I have a sheet named 0001-2011
On this work sheet I have a table that has drop down menus under Artikl, Dimenzije, Dimenzije.
I'm using

Please Login or Register  to view this content.

in the drop down menu under Colum Dimenzije
And when i pick Cev under colum Artikl I can't pick anything under colum Dimenzije.
Does anyone know why? I'm working my *** of here

Please help.

Denis

[DonkeyOte]

You can't use INDIRECT with a Dynamic Named Range directly.

With D4 the active cell create another named range as follows

Please Login or Register  to view this content.

Then modify D4 Validation Source to:

Please Login or Register  to view this content.

another alternative is to use a CHOOSE construct in the Source for the DV listing each range individually (thereby avoiding need for INDIRECT altogether) but this can be cumbersome to manage.

[DonkeyOte]

Should add, another option would to be define all of the options using a non dynamic range and then use a dynamic construct in the validation itself, eg:

Please Login or Register  to view this content.

then for your Validation cells (ie with D4 etc selected) you can have a Source of:

Please Login or Register  to view this content.

obviously above is based on UK Delimiters etc...

[stojko89]

Ok...I don't understand your 2 options

Can I use Colum Artikl to be dynamic and in Colum Dimenzije the drop down menu not to be dynamic so I have it like this.

Artikl:

Please Login or Register  to view this content.

And under Colum Dimenzije

Please Login or Register  to view this content.

but it still doesn't work.

And why when I make a dropdown menus and select cells like A2:A15 and the Cells A2:A6 have stuff written in them and cells from A7:A15 are empty.
So when I make a drop down menu and I select that it doesn't count empty Cells.
When I press the drop down menu the empty cells are there.
Why is that?
Or what would be the best way to do this?
I would need them bouth to be dynamic, couse I would add new stuff under bouth colums most of the time.

[stojko89]

Ok so this somehow works now, but my problems still are the empty cells.
What can I do so it wouldn't count the empty cells in the drop down menu?
Couse If I pick cells from like A1:A100 and only 15cells have data in it all the other would be empty, but it would allow me to add new data when I would want and I wouldn't have to expand the cell picks every time I added data to a cell from A1:A100 if you know what I mean.
You can see in the attachment.

[DonkeyOte]

I don't understand your 2 option

Did you try them ? I thought the instructions were fairly straight forward myself (delimiters aside).

Obviously it is up to you in terms of how you go about resolving the issue but the prior suggestions do as requested.

For the sake of confusion I've attached your original sample file with both the Evaluate and Dynamic Source suggestions in place
(the 2nd option is demonstrated in Column J so you can see both approaches would return identical result)

Note: the use of XLM call in the first option requires .xlsm file extension in XL2007+

[stojko89]

I will use the XLM version thank you very much

[stojko89]

I just have to say it work they way I wanted it to and thats realy great Thank you very much DonkeyOte

[stojko89]

Hello again.

I just have one problem, How can I make the

Please Login or Register  to view this content.

Dynamic so when I copy the table from list 00001-2011 to 00002-2011 the Evaluation would change to

Please Login or Register  to view this content.

[DonkeyOte]

Try modifying the Name RefersTo to:

Please Login or Register  to view this content.

[romperstomper]

Using the ! reference is unreliable in my experience. You are better off using INDIRECT.

[DonkeyOte]

Interesting.

@stojko89, on that basis given you need the B4 reference to be relative in terms of row, with D4 the active cell refine the RefersTo of the name to:

Please Login or Register  to view this content.

[stojko89]

Yes!!! DonkeyOte you're my hero :D
Thanks alot man