Excel 2007 : Drop down validation AND dynamic list

[teddy.montoya]

Hi,
I would like some help with setting up a drop down list that is managed similar to validation data (restricting the user to only select what is in the list) AND easy to add to the list like (making it dynamic - similar to using the formula, =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Any assitance would greatly be appreciated.
Thanks,
Teddy

[sweep]

Hi,

Use your dynamic range definition to create a named range, then refer to the range in the data validation diaoge eg

Named_Range = =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Data validation allow - list
source = Named_range

[teddy.montoya]

If I followed your instructions correctly, this almost works. I would also like to enforce/restrict users to enter the grammar specific (capital first letter followed by lowercase), for example “Teddy” not “teddy”. Attached is the sample I created
On Sheet 1: the dynamic list of Reporters
On Sheet 2: the pull down with the list of Reporters (this allows for “Teddy” and/or “teddy” – which I would like to enforce/restrict to only “Teddy”

Please let me know if this would be possible,
Thanks,
Teddy

[sweep]

Hi,

try this:

=AND(CODE(LEFT(C1,1))>=65,CODE(LEFT(C1,1))<=90,ISNUMBER(MATCH(C1,Reporters,0)))

[teddy.montoya]

Sorry, not following where to use this code? I tried replacing the code you provided with the defined name Reporter {Reporters=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)}. When I do this the pull down is blank. Any suggestioins? Sorry, if this is supposed to be easy.

[JBeaucaire]

Nevermind...you have hidden spaces in your data that made for a false test.

[JBeaucaire]

I added a conditional format to your column C that will hide the font if you type something that isn't exactly the same as the original values in the Reporters list.

[sweep]

The formula should be used in Data > validation > custom.

However, as JB mentions, you have spaces in the list that cause issues.

[JBeaucaire]

The formula should be used in Data > validation > custom.

Sweep, I opted for conditional formatting because the main point of this issue is that Data Validation Lists are already being used.

[sweep]

Understood, I was just pursuing the original request for validation.

Rgds,

Dave

[teddy.montoya]

Thank you both for your help and patience, I really appreciate it.

[teddy.montoya]

I do not have the option to close this case/tread - I dont have any pull down option near the "Titlle" (as indicated in the FAQ). Any suggestions?