Dzisiaj chciałbym opisać bardzo ciekawą sytuację, która przydarzyła mi się wczoraj podczas testowania mojej aplikacji w biurze Klienta. Rzecz dotyczy zastosowania mechanizmu sprawdzania poprawności w wersjach 2007 i 2010 Excela.
Lista w Drugim Arkuszu
Załóżmy, ze chcemy ograniczyć możliwość wpisywania danych w kolumnie A do stu różnych kodów. Nie chcemy jednak tworzyć listy rozwijanej, ponieważ byłaby obszerna i znalezienie odpowiedniej pozycji trwałoby zbyt długo. W tym celu umieszczamy unikatową listę kodów w drugim arkuszu i jako źródło w sprawdzaniu poprawności podajemy formułę
= LICZ.JEŻELI(Arkusz2!$A$1:$A$100;$A2)=1.
Ta sprytna formuła sprawdza czy kod wpisany przez użytkownika w komórce A2 znajduje się na liście kodów w drugim arkuszu. Jeżeli tak, wówczas wszystko jest OK – wpis zostaje zachowany. W przeciwnym wypadku wyskakuje powiadomienie, że kod jest nieprawidłowy i nie zostanie wpisany do komórki.
Właśnie z takiego rozwiązania skorzystałem tworząc projekt w Excelu 2010. W trakcie testów podawałem nieprawidłowe kody, i faktycznie Excel nie pozwolił mi ich wprowadzić do komórek w arkuszu pierwszym. Zadowolony z takiego obrotu sprawy wysłałem na potwierdzenie screena z komunikatem i wziąłem się za dalszą część projektu.
Zgłoszony Problem
Ku mojemu zaskoczeniu otrzymałem informację, że sprawdzanie poprawności nie działa i wciąż można podawać nieprawidłowe kody w pierwszym arkuszu.
Po chwili zastanowienia uznałem, że użytkownik nie wpisał kodu „z palca” tylko skopiował go z zewnątrz lub przeciągnął komórki. W takiej sytuacji mechanizm sprawdzania poprawności zwyczajnie bierze w łeb.
Wdrożyłem więc makro, które blokuje możliwość kopiowania i wklejania w pliku, a także uniemożliwia przeciąganie komórek. Odesłałem nowy plik z korektami. Okazało się, że sprawdzanie poprawności dalej nie działało….
Excel 2007 vs Excel 2010
Wczoraj udałem się do Klienta, gdzie omówiliśmy m.in. tą zagadkową kwestię sprawdzania poprawności. Otwierając plik w Excelu 2007 ku mojemu zaskoczeniu okazało się, że w kolumnie A nie ma żadnych reguł sprawdzania poprawności! Nic więc dziwnego, że użytkownik mógł wpisać dowolną wartość…. Ale zaraz. Przecież zapisywałem plik z tymi regułami na 100%. O co więc chodzi?
Postanowiłem wykonać tą samą operację, co u siebie na Excelu 2010 – czyli wprowadzić formułę z funkcją LICZ.JEŻELI. Po wprowadzeniu formuły do okienka Excel 2007 jednak zaprotestował. Pojawił się komunikat, że w formułach nie mogę odwoływać się do zakresów, które znajdują się w innej zakładce. Ciekawa sprawa, ponieważ w Excelu 2010 nic takiego się nie pojawiło.
Rozwiązanie Problemu
Postanowiłem obejść problem sprytnie, definiując nazwę. Chodziło mi o to, aby zamiast do zakresu umieszczonego w drugim arkuszu, odwołać się właśnie do konkretnej nazwy. Tym razem Excel nie zaprotestował przy wprowadzaniu formuły – byłem z siebie dumny, że udało mi się go przechytrzyć…
Szybko jednak okazało się, że moja radość jest przedwczesna. Mimo tego, że formuła została zaakceptowana, to nie działała. W komórkach wciąż można było wpisywać nieprawidłowe kody.
Uznałem, że trzeba w takim razie po prostu umieścić listę kodów w pierwszym arkuszu – czyli w tym samym, w którym mamy sprawdzanie poprawności.
I faktycznie tak zrobiłem, utworzyłem listę w kolumnie, która nie brała udziału w obliczeniach i ukryłem ją. Tym razem wszystko zadziałało prawidłowo. Excel nie zgłosił protestu przy wpisywaniu formuły. Walidacja danych blokowała niepoprawne kody. Problem został rozwiązany.
