dynamic validation list

  • Thread starter Thread starter Stefi
  • Start date Start date
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
 
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
|
 
Back
Top