0

(I am using Excel in french)

I am updating and cleaning a worksheet with validation dropdown.

The values from these dropdown come from one of the 32 named ranges of the worksheet (ListeOP, Liste1000, Liste2000, etc.) depending other cells.

Currently the cells validation is:
- Authorize: List
- Source (unfold) :

=INDIRECT(
  SI(
    ET(A8<>"";A8=B8);
    "ListeOP";
    SI(
      A7<>"";
      CONCATENER(
        "Liste";
        A7
      );
      SI(
        A8=B8;
        "ListeOP";
        CONCATENER(
          "Liste";
          A8
        )
      )
    )
  )
)

This work but is really inreadable and difficult ton maintain. I wanted to create a VBA function to determine which named range to use.

Public Function GetActivityComboBox(activity As Integer, family As Integer, familyTop As Integer) As String
    If (family > 0 And activity = family) Then
        GetActivityComboBox = "ListeOP"
    ElseIf (familyTop > 0) Then
        GetActivityComboBox = "Liste" & familyTop
    ElseIf (family = activity Or family = 0) Then
        GetActivityComboBox = "ListeOP"
    Else
        GetActivityComboBox = "Liste" & family
    End If
End Function

And the new validation source:

=INDIRECT(GetActivityComboBox(B8;A8;A7))

But I get the error: Impossible to find the named range you specify (translation)

I already checked my VBA function returns the right range name.

What did I miss ? or does anyone has another idea to obtain the same behavior (in few words: variable validation list)

Mistic
  • 1,377
  • 2
  • 13
  • 27
  • 1
    Try using a `MsgBox` after changing your function to a Sub and then dumping the resulting string MANUALLY into an `=Indirect()` formula. You are saying that the VBA returns the correct string and you are also saying that your previous formula worked. If that part is working I see no reason why the string produced by the VBA wouldn't work in an `Indirect()` formula. – Chrismas007 Dec 23 '14 at 15:06
  • Also, you can really clean up your nested `If` statements by employing the `ElseIf` syntax... [Found Here](http://www.techonthenet.com/excel/formulas/if_then.php) – Chrismas007 Dec 23 '14 at 15:07
  • I believe [this](http://stackoverflow.com/questions/4783019/can-i-use-vba-function-to-return-a-dynamic-list-of-acceptable-values-into-exce) will give you some guidance as to why your data validation is not working as expected. – TheEngineer Dec 23 '14 at 15:13
  • @Chrismas007 `=INDIRECT("ListeOP")` indeed works. @TheEngineer I don't really understood what is going on in this thread, looks like he tries to directly return the list of values, which is not my case. – Mistic Dec 23 '14 at 15:55

0 Answers0