dynamic validation list

S

Stefi

Hi All,

I have a dynamic validation list and its drop down list works well but it
allows inputting any other values as well. The source formula is this:

=OFFSET(gépkocsik,1,MATCH($C14,OFFSET(gépkocsik,0,0,1,COLUMNS(gépkocsik)),0)-1,COUNTA(OFFSET(gépkocsik,1,MATCH($C14,OFFSET(gépkocsik,0,0,1,COLUMNS(gépkocsik)),0)-1,ROWS(gépkocsik),1)),1)

C14 contains the manufacturer, the drop down list consists of its models.

Named range gépkocsik is this:

Mercedes Toyota Ford
Sprinter 313 HiaceLH112 Transit2.0
Sprinter314 HiaceLH118 Transit2.2
Sprinter314 4wd HiaceLXH12 Mondeo2.0
Sprinter313/35 HiaceLXH28 Galaxy1.9
Sprinter313/35 4wd HiaceRCH22 Focus1.6
Sprinter313/36 HiaceRCH23 Fusion
Sprinter315/36 HiaceKLH22
Sprinter412 Avensis
Vito CarinaII
Vaneo Corolla1.8
Corolla2.0
Landcruiser

What should I do to prevent inputting anything else than elements of the
drop down list?

Thanks,
Stefi
 
J

Jim Rech

If you have blank cells in your validation list range Excel will allow any
entry. You should make sure that your dynamic source formula doesn't
include any.

--
Jim
| Hi All,
|
| I have a dynamic validation list and its drop down list works well but it
| allows inputting any other values as well. The source formula is this:
|
|
=OFFSET(gépkocsik,1,MATCH($C14,OFFSET(gépkocsik,0,0,1,COLUMNS(gépkocsik)),0)-1,COUNTA(OFFSET(gépkocsik,1,MATCH($C14,OFFSET(gépkocsik,0,0,1,COLUMNS(gépkocsik)),0)-1,ROWS(gépkocsik),1)),1)
|
| C14 contains the manufacturer, the drop down list consists of its models.
|
| Named range gépkocsik is this:
|
| Mercedes Toyota Ford
| Sprinter 313 HiaceLH112 Transit2.0
| Sprinter314 HiaceLH118 Transit2.2
| Sprinter314 4wd HiaceLXH12 Mondeo2.0
| Sprinter313/35 HiaceLXH28 Galaxy1.9
| Sprinter313/35 4wd HiaceRCH22 Focus1.6
| Sprinter313/36 HiaceRCH23 Fusion
| Sprinter315/36 HiaceKLH22
| Sprinter412 Avensis
| Vito CarinaII
| Vaneo Corolla1.8
| Corolla2.0
| Landcruiser
|
| What should I do to prevent inputting anything else than elements of the
| drop down list?
|
| Thanks,
| Stefi
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add Item to Data List that has Dynamic Source 1
Data Validation with dynamic ranges 2
Dynamic Drop Down List 5
Adding to a data validation list 2
Drop down list from index/match formula 1
How do I set up a Dynamic Named Ranges in Excel 2007 7
Use word to associate an item in validation drop down list 2
Changing named Validation list to Dynamic list. 1

Top