Dependent Validation where first list is variable length (indirect function does not work)

[jekster]

I have been puzzled by this for some time and seen various other dependent validation examples but cant seem to configure them to work with my sheet

First sheet lists sheets in workbook and therefore has variable length. It is updated by a macro to ensure its current when entering the sheet containing the list or the calculation sheet.

There is a calculation sheet which I want to select options based on dependent validation i.e. type then ingredient

The remianing sheets group ingredients by type

I cant seem to get the second level of the dependent list i.e. Type Flour to then show the list of Flour types The flour type named list will also need to be variable in length to allow for additions (I would like to make it so the lists are not constrained to any particular length but if this has to be done then so be it)

From what I gather, the INDIRECT function cannot be used with a variable list? I have seen the INDEX function as a suggestion but cannot figure out how on earth to use it with this... please help

(my bigger project would be figure out how to scrape prices from the web to keep information up to date but thats not essential to me....)

Recipe Cost Calculatorv Master.xlsm

[Søren Larsen]

Using =INDIRECT() is actually working when I look at it. I guess your problem is that the list you get in C4 on "Calculator" only consists of "Ingredients" when choosing "Flour" in B4, right?

The reason for this is that you have 2 named ranges called "Flour". And the one you want to use is only available on the sheet "Flour", whereas the range that is available in the entire workbook only contains 1 value; "Ingredients".

So, what you need to do is to make sure that the "Scope" of the named ranges that you want to refer to on "Calculator" is "Workbook". Therefore you need to delete the named ranges called "Flour", and create a the correct one again.

Does this make sense?

[jekster]

Thansk for the quick response.. Just realised I had sent the wrong one... I spotted that issue and got it working with a defined named range and the indirect function however to make it tidier similar to the first list (with the Type/sheetnames) which adjusts for the variable length of the list in the dropdown and uses the following formula to define the named range =Sheetlist!$A$3:INDEX(Sheetlist!$A:$A,COUNTA(Sheetlist!$A:$A)+1)

I really wanted (thought) I could use the same functions to define the dependent list named range with a variable length for all the ingredient sheets and I used the following for the Flour list =Flour!$B$4:INDEX(Flour!$B:$B,COUNTA(Flour!$B:$B)+1) but if I use the INDIRECT function in the validation with this it evaluates to an error... my current verson of the spreadsheet is attached.

Also Excel "appears" to create named lists all of its own accord and I have to keep deleting them... that is how I ended up with 2 lists named flour... not sure how this happens and why excel does it!!??

[Søren Larsen]

Ahh, I see. Well, you can't do it in the way you are trying to. But there is a good guide on how to use a different method to achieve the same here:
http://www.contextures.com/xlDataVal02.html
Look under the last heading called "Using Dynamic Lists"