• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic range in dependent validation list

S

scavutti

New Member
Some days ago I read about a drop-down list that shows a list depending on the content of another cell. That's nice! It works great, except for dynamic ranges. When the source of the list is this: =IF(A1="",W,INDIRECT(A1)), it only works when the lists are hard coded. That makes it a little awkward to add items to the list. I can solve this with VBA, but maybe there's another solution?

In the example file range F is dynamic. When A1=F, then the list doesn't show. Evaluating the formula results in #REF.

When the dynamic range is not used in a formula, but addressed in the source of the list, it's ok (see cell E1). I cannot figure out why.

For the file: http://dl.dropbox.com/u/54069060/DR_DepDataVal.xlsx.
 
Hi ,


The technique to do what you want to do , is explained in this link :


http://www.contextures.com/xldataval02.html


Your file , revised to show this technique , is available at this link :


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21171


The revision has been done for two countries GB and FR only.


Narayan
 
Thanks Narayan,


I'll take a look at your file later this day. First I'm going to take a look following the link.

/René
 
You must log in or register to reply here.
Back
Top