View original (French)

How to combine OFFSET and INDIRECT functions in Excel?

Solved
yisma Posted messages 6 Status Member -  
 tintin -
Hello everyone,

Here is my problem,
I am currently creating successive drop-down lists in Excel (when you select a value from one drop-down list, a new associated drop-down list appears).
For this, I use the function ""= INDIRECT()"".

Since my data ranges are on another sheet, I give them a name.
When these ranges are fixed, everything works perfectly, but when I want to make these ranges modifiable using the function ""= OFFSET($A$2;0;0;COUNTA($A:$A)-1;1)", nothing works...

The cell using the "indirect" function does not display the data from my modifiable range.

I am wondering if these two functions can work together or if I need to find another way to create conditional drop-down lists based on changing data ranges.

If you have ever faced a problem like this, please share your thoughts with me, I am sure they will greatly help me in this undertaking.

Thank you and best wishes

Arnaud
Configuration: Windows Vista Firefox 3.5.2

7 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    indeed, I was very surprised too!

    check if this solution works for you with an intermediate column to avoid the INDIRECT function
    https://www.cjoint.com/?jdmrDtgrNN
    --
    Best regards, Michel
    2
  2. yisma Posted messages 6 Status Member
     
    Not a bad idea,
    But I'll need to look into this SEARCH function.
    Because for ergonomic reasons (multiple lines with a dropdown list to follow), I can't insert an intermediate column in that specific spot.
    However, if I create these intermediate columns in another sheet specifically for that, is it still possible to use the SEARCH function?
    Following your example, I would then need to name the cell $I$2 and the range B$2:G$15 and call them using "search".
    What do you think?

    Arnaud
    0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    A priori, no problem, especially if you name I2 and the range, the trick being to transfer the data into a single intermediate range (which I named cascade)

    but you can very well place cascade in an unoccupied corner of your sheet: I had placed it in column H2:H15 (if I remember correctly) but you could put it in AA2....

    you say because I'm going to insert this thing into my "list box" workbook where I handle different cases of data validation.

    --
    Best regards, Michel
    0
  4. yisma Posted messages 6 Status Member
     
    Try this too, there is another solution to fill a range, just "insert" instead of adding the item and the range expands by itself!
    The remaining issue is that if we want to automate everything and enter this new data via a form on another page, how to feed the correct column?
    0
  6. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Yeah, but it seems complicated to make a userform with a combobox and one or two listboxes (add-remove) just to avoid an intermediate column.
    Well, you see... and you show me, thanks in advance
    --
    Best regards, Michel
    0
  7. tintin
     
    I have the same problem, it’s annoying!!!

    Moreover, I don’t understand why Excel doesn’t accept this workaround...
    0
  8. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello everyone,

    Try it like this:
    -select the list with the header
    -menu 'data / list / create a list...'
    -select the items and name the range

    If you add or remove an item, Excel will manage your range dynamically.
    I don't have time to test with indirect(), but since it's a simple name, I think you have a good chance...

    Eric
    0
    1. tintin
       
      Thank you Eriiiic for your response.

      I have browsed a good part of the internet and I noticed that the combination of cascading VALIDATION LISTS, INDIRECT NAMES, and OFFSET does not work in Excel. There are many posts on different forums highlighting the issue and no solutions...

      Then I found this at Boisgontier: http://boisgontierjacques.free.fr/pages_site/listes_cascade.htm

      Thanks to him!

      I think we can mark this post as resolved because the proposed solution works very well.
      0