Listbox does not update using a dynamic named range with Offset function

Hi All

I have created a dynamic listbox (form control) which updates based on the named range,however when
I incorporate the offset function to dynamically resize the list, the listbox fails to recognize the named range.
I'm aware that using an Active X control with VBA, I can achieve the goal but just wanted if anybody could come up with a non vba solution.
Any tips much appreciated.

Thanks

are you using this listbox on a sheet or userform?
are you using the offset function in the named range or have you used the formula in the input range of the form control

do you need a listbox? can you use data validation? i usually used a named range which has the formula in it.

I am using a List Box on a Sheet. The offset function is used in the named range and I would prefer to have a listbox cause I'm creating a dashboard.

what formula are you using?

i have tried this and it works

Hi Sean

Thanks for your feedback. I have no issues when there is a single list however with multiple lists, the listbox
fails to recognize the named range. Attached is a sample file .
Cheers

Not sure I am getting enough of the whole picture. Would a 2 dimensional Dynamic Named Range be of use for populating the dependent list?

Formula:

Please Login or Register  to view this content.

I've used these before and then referenced the columns of the named range with INDEX/MATCH combinations. You have a lookup value that shows in B1 resulting from the cell link in A1. I would think that could be of service for this.

I've only used these in data validation / drop-downs.....never tried to apply them to form controls.

To confirm that the formula works copy and paste it into the name drop down box and hit enter. That should select the current range of cities by country.

OK. I tried something and I believe it does what you want.

In addition to the Dynamic Named Range I posted in post #7 I added this Dynamic Named Range using INDEX / MATCH referencing the first DNR.

Formula:

Please Login or Register  to view this content.

I named the first DNR CitiesByCountry. This one I named PickaCity. I applied
it to the second listbox.

I hope the rest explains itself. The file is attached.

Thanks mate. I tried populating additional entries under column G (ie List for Australia ) and it works, however when I update
the other two, it fails.

Originally Posted by Mysore

Thanks mate. I tried populating additional entries under column G (ie List for Australia ) and it works, however when I update
the other two, it fails.

I tried this with a static range $G$3:$I$20 replacing the 2D DNR equation. The second listbox updates no matter the number of row under any country up to 18 items. You'll need to manually expand the range to accommodate more than that.

Thanks buddy. For time being I might have to stick with Static range until I find a solution.
Thank you all.