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
"Stefi" wrote in message
...
| 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
|

Please, ignore this post, I found the answer on
http://www.contextures.com/xlDataVal08.html#Invalid
Stefi


€žStefi€� ezt Ã*rta:

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

Or uncheck Ignore blank cells as I learned from
http://www.contextures.com/xlDataVal08.html#Invalid

Thanks, Jim!

Stefi

€žJim Rech€� ezt Ã*rta:

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
"Stefi" wrote in message
...
| 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
|