Share via

reference to dynamic range with indirect function leads to error in evaluation

Anonymous
2011-01-16T18:47:57+00:00

I have a dynamic range defined based on a  column which comes from a pivot table.I want to use that range as source of data for data validation.When I define the range name for the data validation all goes wonderful. However when I put the name of the range in a cell ($A$4) as a text string and use =INDIRECT($A$4) for the validation rule then I get : The Source currently evaluates to an error. Do you want to continue?What am I doing wrong?I have done a lot of googling but did not find the solution. Hope someone can help me.Albert ****

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-02-04T17:14:42+00:00

    Hi,

    Your range E14:E17 is static. I am talking defined named range dynamically set using an OFFSET formula.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2014-02-03T23:36:41+00:00

    Hi,

    I tried this in Excel 2013 and it worked fine

    1. Assigned a name, say Critical, to a range E14:E17
    2. Typed Critical in cell A1
    3. In cell B1, I went to Data > Validation > Allow > List > Source

    =INDIRECT(A1)

    The process above worked fine for me.

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2014-02-03T17:07:39+00:00

    I don't know if it will ever be fixed.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-02-03T16:16:37+00:00

    When is it going to be fixed?

    Was this answer helpful?

    0 comments