Validation Dependent Dropdown AND Auto Redefining Validation List Source Range

[DDePalma]

I have the "Learn Excel 97 through Excel 2007 from MrExcel" book.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I was successfully following the “Use Validation to Create Dependent Lists” on Page 900.<o></o>
Everything was working great.<o></o>
I then tried to use the “Allow Validation Lists to Automatically Redefine as They Grow” on Page 895.<o></o>
The “Allow Validation Lists to Automatically Redefine as They Grow” uses the =OFFSET(A1,0,0,COUNTA(A:A),1)<o></o>
To define the named range. The “Use Validation to Create Dependent Lists” uses the =INDIRECT( ) to pass the<o></o>
Result from the parent dropdown selection to the Validation source.<o></o>
<o></o>
This stopped working when I added the “Allow Validation Lists to Automatically Redefine as They Grow” from Page 895.<o></o>
<o></o>
Can you help?<o></o>

 

[jc.021286]

never read the book,

though with regards to the allowing the list to grow.
While performing the validation in 2007.
Data> Data Validation,

Select list from the settings,
for the range that is to be selected, you would want this to grow to encompass more information, thus the offset and counting

so if I have names in column A I can set the formula to equal =(offset(A1,0,0,counta(A:A),1) thus, saying that I want the list to start in cell(A1), shift neither row nor column, but expand the range by the count of nonblank cells in column a and be 1 column in width.

Therefore, if I have 10 names in column A, my list will go from A1 through 10 down, A10. If I add more names, it will automatically expand the offset formula to encompass new data entries.

I hope this helps you at least a little bit...
jc

 

[DDePalma]

Thank you for your quick response.
You have validated what I was trying to do and it works great.
The problem is when I combine this with a dependent drop-down list.
The instruction is to use a =indirect () for the source of the validation list.
It all worked [the =indirect() and the =(offset(A1,0,0,counta(A:A),1)] when the named range was static. once I changed the named range to the "grow" as the list grows ... I started to get a REF error for the =indirect().

 

[jc.021286]

Then might I suggest something such as this:
"
The real power of the INDIRECT function is that it can turn any string into a reference. This includes any string that you build up using string constants and the values of other cells in the formula, strung together with the & concatenation operator. For example, the simple formula
=SUM(A5:A10)
will sum the values in the range A5:A10. However, suppose you want to be able to specify which range of rows to sum "on the fly", without having to change the formula. The INDIRECT function allows you to do this. Suppose you put your starting row cell B1, and your ending row in C1. Then, you can use the formula
=SUM(INDIRECT("A"&B1&":A"&C1))
The argument to the INDIRECT function is
"A"&B1&":A"&C1
"
http://www.cpearson.com/excel/indirect.htm


I can only suggest that you try to get your =indirect() function to work on its own before trying to input into the list function.
Thus, try playing with it in the manner specified in the quote and see if that will allow you to get the results you require.


?=indirect("A1:A"&B1)? to give you a range that you can manipulate with the B1 cell.