|
dynamic validation list
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 |
dynamic validation list
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 | |
dynamic validation list
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 |
dynamic validation list
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 | |
| All times are GMT +1. The time now is 11:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com