Dependant drop down lists

[jono19872002]

Hi
I am trying to create dependant drop down lists but am currently having issues.
I understand the concept and can get it working when I hardcode the ranges as a named range, however when I use the offset function in the name range I cant get the dependant drop down list to work.

For example
I have a "Category" named range which is Income, Expenditure, Assets, Liabilities, which works fine and is input via a dynamic named range (similar to below)

I also have a named range called Expenditure with the names range as below:

=OFFSET(Categories!$D$1,1,0,COUNTA(Categories!$D:$D)-1,1)

This on its own works fine.

However when I try and use the =indirect() to pick up the expenditure named range based of the category named range
I get the error message "The source currently evaluates to an error" ?

 

[oldbrewer]

height is counta ($d:$d) -1

so if cells d1 to d5 ONLY have non blank cells height is 4

put 4 in your formula instead of the counta.......... does it work then?

 

[jono19872006]

Hi
There is data in column D.

So with the dynamic defined range, in a cell when I add in the data validation I can reference this in the drop down and it works ie =category.

However if I type the defined range name in a cell eg typing category in cell f2 , It wont let me reference that cell by the indirect function to access the defined range in the data validation eg =indirect(f2).

but when I save a normal defined range eg =Categories!$D2:$D5 then I can reference this by the indirect function in a different cell

 

[oldbrewer]

ok sry I cannot help, but RICK will when he sees this post