INDIRECT function does not work when referencing to a dynamic list

  • Good afternoon,

    I am trying to link a dynamic list to the value of a cell (D2) immediately to the left (reference cell). The reference cell (C2) is itself is populated by the user (it is a list of country codes). In other words depending on the country code selected in C2 the user will then be confronted (in D2) with a dropdown list dependent on what country code they select. E.g. for the country code UK I have: Ambleside, London,Manchester set up as a dynamic list (which will expand as and when I add to it). In D2 I set the cell up as a list with "=INDIRECT(C2)" (without quotations) but I have found that it does not recognise this (i.e. no dropdown list appears.

    I have found that if I use a non-dynamic list this works... so assume it is something to do with trying to use a dynamic list.

    Can anyone advise whether there is a work around or if I am doing something wrong?

    Thanks.

    Paul

    (Ps. I was on here before but lost my login details).

  • Hello Paul,


    Why don't you attach a small sample file ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sample attached. The columns with a light tan colour are those in a separate sheet. Each column is a named dynamic range (the name in row 1). What I want is to be able to use the indirect function in column D that will show a dropdown list independent on what is selected in column C. Thanks. sample.xlsx

  • Hello,


    Thanks for the file ...


    Have not checked it yet ... but using a 2 Letter ISO code for each country might create a conflict with the Columns which have the same 2 Letter identification ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,

    Thanks for the quick response. I tried changing to a 3-digit name but still no luck. I can see the logic in your point although if I create a non-dynamic list it will drop down.

    Look forward to seeing what you can do.

    Paul

  • By the way ... a couple of other solutions


    1. to just reproduce the dynamic range name formula inside Indirect() .... or, even easier,

    2. to use a Table reference ...;)


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • FYI, you are correct that INDIRECT does not work with dynamic ranges - though it will with Tables.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • This example demonstrates how to use a Table to create dynamic lists

  • Glad to hear you could fix your problem :)


    Thanks for your Thanks :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)