List Box (form control)

[cata2200]

Hello

I am searching for a solution of my problem, but no chance to to locate it.

I have a List Box with 4-5 options (one at the time).
Based on the choosen option I would like to assign the already defined list in a second List Box.

So:
In the first List box I have: "Hired", "Off Hired", "Standby", "Abandoned"
If chosen the first option ("Hired"), the elements in the second List Box shall be taken from the "Vessels_Hired" array, which is already defined as:
=OFFSET(Lists!$F$6, 0, 0, COUNTA(Lists!$F$6:$F$100), 1)

Any clue?

Regards
Catalin

 

[Weaver]

How are the lists for the other choices defined?

There's a good link for this on Ozgrid (or it might be Contextures) but I can't find it.

 

[Weaver]

PS is this in a worksheet or on a user form?

 

[cata2200]

Hello Weaver

Thanks for reply.
1. All lists are defined with Names Manager
2. The List Boxes are in a worksheet. (Sorry for confusion for Form Control).

Regards
Catalin

 

[Weaver]

Is it convenient to have Off Hired as "Off_Hired" (note the underscore) in the drop box?

If so, it makes things real easy

Create 4 named ranges, 1 for each list, and name them exactly as they'll appear in the drop list. (from your post, you'd need to rename 'Vessels_hired' as just 'Hired', or have it appear in the list as 'Vessels_Hired')

Suppose this first list is in cell A1

Then for your second list, use

=indirect($A$1)

for the location of the list and everything should work as needed.

HTH

 

[cata2200]

Weaver

Thanks a lot, buddy. I done it already. Sorry not to close the subject.
It is almost the same solution, just i bit longer.

Yes, indeed, I have 5 lists already nominated as mentioned by you - as dynamic array. I have used the "magical" Aladdin solution: transposed the Main database with PT, in separate working sheets and a "macro" to refresh them "automatically".
Then I have created a temporary list, dynamic located, named: Temporary_list1. The population of this one is using INDIRECT function as well as a "play" with the length of the array, from 1 to the final element.

However, many thanks for support.

Regards,
Catalin

PS Send me a PM with your email address, if possible.