Indirect data validation with dynamic range not working.

All,

Having tried to sort out another OP’s thread (http://www.excelforum.com/excel-prog...an-option.html), I found an issue which I cannot seem to fix, and so thought that someone else on here could once again broaden my excel skills!!

In a nutshell, the attached workbook has two sheets, and essentially the user can pick a type from Column B on the master sheet and then they chose whatever corresponding items (from Column C) and the remaining columns calculate cost associated per selection.

This currently works fine, BUT, the lists (in Column C, and shown in the lists worksheet) are not dynamic and so if the user wishes to change the number of items etc, then they need to go to the Name Manager and update ranges on the Lists worksheet manually.

I realize that this is due to the Indirect source I have used in the data validation for Column C, but I simply cannot see a way round it, and therefore hope that someone can shed light on this.

I have searched the forum, but nothing seems to work 100%, and I would be delighted if somebody could show me how this is completed, as this is something I could use in my workplace!!

Thanks for looking.

Skyping
P.S Please see the attached file for clarification etc.

Short story: you can't use INDIRECT with Dynamic Named Ranges.

Better story: see recent thread: http://www.excelforum.com/excel-form...ml#post4595095

You're welcome. Thanks for the rep.

Thought there may be a workaround so not to use tables, but apparently there isn't.

There may be a workaround but it looks a bit messy to set up. If you want to avoid Tables (but why?), you might want to try this approach:

http://www.contextures.com/xlDataVal02.html#Dynamic

That said, I spent a long time trying and failing to produce a solution to the other thread before it dawned on me that the problem was the Dynamic Named Ranges and then I resorted to the Table route. Easy to set up and nothing to worry about.