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

Question

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 ****

Answer 1

INDIRECT does not work with dynamic ranges. See the discussion in http://www.dailydoseofexcel.com/archives/2005/03/01/indirect-and-dynamic-ranges/.

Answer 2

That works for me too.  You can also enter Indirect as an array formula.  If E14:E17 has for values "A", "B", "C" and you enter "=INDIRECT($A$1)" as an array formula in B1, and then drag down to B3, you will get the values in E14:E17.

Answer 3

Hi,

In which case, my method described earlier should work

1. Ensure that in cell E13, there is heading in Bold
2. Select E13:E17 and press Ctrl+T
3. Now follow the steps mentioned in my previous post

As and when you add data beyond cell E17, the validation list in cell B1 will expand.

Hope this helps.

Answer 4

Hi,

Thanks for the reply,

Yes. Eventually data will be entered below row 17, and I would like 'Critical' range to expand automatically to the very last entry below E14:E17

Let me know if you want a detailed scenario,

Answer 5

Hi,

By dynamic named ranges, do you mean that data will be added beyond row 17.  If and when that happens, you want the validation list in cell B1 to update?

Please clarify.