• 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.

Named ranges in Datavalidation with indirect formula

J

Juultje

New Member
Hi all,
I have 'inherited' an excel workbook with several sheets and loads of formulas from a former colleague. Now someone 'broke' the drop downs and eventhough I fixed most of them, I get stuck with one particular drop down.

In sheet 1 I have 3 columns with data validations (info comes from a second sheet).
Column A gives a drop down with project names. Column B gives a drop down with sub-project names based on the choice made in column A. Column C gives a drop down with topics based on the choice made in column B. The information in sheet 2 is grouped into named ranges.

All drop downs work except for 1. I can select all projects in column A without problems. However for 1 project I cannot choose a sub-project (column B). I've checked in the Name Manager if all cell references are okay and they are. I've checked the data validation for this particular sub-project. It contains an Indirect formula --> =INDIRECT($F14)
Eventhough it looks the same as the others it does not 'go' anywhere.
(I've read lots of posts about the Indirect formula to understand the principle but I'm stuck anyway...) And I'm sure there is a IF formula in there somewhere as well.
Unfortunately I cannot upload the file, it contains confidential information.

So finally my question:
What do I miss? Why does my data validation not work?
 
Hi Juultje,

I had a similar kind of problem while sharing confidential information with dozens of Fields.
I have made a sample workbook which took me half day & I got the solution in less than an hour.

Special Thanks to SM.

Hence, I suggest you sharing a sample sheet would be easier for all who can help you out.

Regards,
AM:)
 
Hi SM,
All drop down data series have a name. They all work, except that one.

@ AM, I wouldn't know where to begin with the sample sheet, but I suppose I could remove the confidential, non essential data from the book... I'll try, thanks for the tip. :-)
 
Hi again,

I've stripped the workbook from all non-essential and confidential data and what's left are the concerned columns in the Team sheet and the two sheets where the data is coming from. Hope this helps...
 

Attachments

@Juultje

W.r.t the file that you have attached, on your Time Sheet there are data validation column C,D & E. Which are totally working fine. Can you elaborate further more what exactly is the problem in what cell?

Regards,
 
Hi SM,
Thanks for your quick response.
In Cell D11the drop down is not working. They are working for all other projects, just not the ICE... project in row 11.
 
SM, you are a true ninja! :awesome:
Can you explain what you did?
And can I remove the columns you've added at the end of the Team sheet?
Rgds, Juultje
 
@Juultje

Thanks for your kind word.

Column R & S were hidden with formula, Row 11 was not having those formulas, so I copied the formula to Row 11 column R & S. Don't remove these columns, just hide them as the second and third data validation are using these column values in INDIRECT function.

Now as to what I did. You named ranges for this paticular value got a different name than what is actual in the list so I just renamed them like below.

ICE_Rollout_DEV_Cosmos -- > ICE_Rollout_Benelux_DEV_Cosmos

So like This I added Benelux in every named range and even on sheet with refrences. This is done because your master dropdown in Column C has this word.


Just see the attached file with full change.

Regards,
 

Attachments

Oh wow, how did I miss the name error?! I feel like I should re-read my excel for dummies... :oops:
Thanks for finding the hidden formulas as well. I knew there had to be something hidden.
Thanks ever so much, you've saved me a world of headache.
Now I'll try to re-produce this solution to my real sheet. I'll let you know if I pass this test.
 
You must log in or register to reply here.
Back
Top