Один з параметрів, доступних при перевірці даних, це параметр Список (List), тобто зручний список, що розкривається, з якого користувачі можуть вибрати певні елементи. Але є один недолік - якщо ви спробуєте послатися на список, що знаходиться на іншому робочому аркуші, то отримаєте повідомлення, що це неможливо. На щастя, за допомогою чергового трюку неможливе можна зробити можливим.
Змусити Excel при перевірці даних посилатися на список на іншому робочому аркуші можна двома способами - за допомогою іменованих діапазонів і функції ДВССИЛ (INDIRECT).
Спосіб 1. Іменовані діапазони
Ймовірно, самий простий і швидкий спосіб виконати це завдання - присвоїти ім'я діапазону, де розміщується список. Для цієї вправи ми припускаємо, що діапазону присвоєно ім'я MyRange. Виділіть клітинку, в якій повинен буде з'явитися цей список, що розкривається, і виберіть команду Дані → Перевірка (Data → Validation). В поле Тип даних (Allow) виберіть Список (List), а в полі Джерело (Source) введіть = MyRange. Клацніть на кнопці ОК. Тепер список (який знаходиться на іншому робочому аркуші) можна використовувати як список перевірки.
Спосіб 2. Функція ДВССИЛ (INDIRECT)
Припустимо, список знаходиться на аркуші Sheet1 в діапазоні $ А $ 1: $ А $ 10. Клацніть будь-яку клітинку на іншому робочому аркуші, де повинен буде з'явитися цей список перевірки. Потім виберіть команду Дані → Перевірка (Data → Validation) і в полі Тип даних (Allow) виберіть пункт Список (List). В поле Джерело (Source) введіть наступну функцію: = INDIRECT ( "Sheet1! $ А $ 1: $ А $ 10"). в російській версії Excel: = ДВССИЛ ( "Sheet1! $ А $ 1: $ А $ 10"). Переконайтеся, що прапорець Список допустимих значень (In-Cell) встановлено, і клацніть на кнопці ОК. Список на аркуші Sheetl виявиться у вашому списку перевірки.
Якщо ім'я робочого листа, на якому розташований список, містить прогалини, функцію ДВССИЛ (INDIRECT) потрібно записати так: = INDIRECT ( " 'Sheet 1'! $ А $ 1: $ А $ 10"). в російській версії Excel: = ДВССИЛ ( "Sheet 1! $ А $ 1: $ А $ 10"). Різниця полягає в тому, що тут після першої лапки варто один апостроф, а другий апостроф знаходиться перед знаком оклику. Апострофи обмежують назва листа.
Корисно використовувати поодинокі апострофи завжди, незалежно від того, містить ім'я, прогалини чи ні. З апострофами ви все так же зможете посилатися на листи з іменами без пробілів, але це і спростить внесення змін пізніше.
Переваги і недоліки обох методів
У іменованих діапазонів і функції ДВССИЛ (INDIRECT) при використанні їх для зв'язку зі списком на іншому робочому аркуші є переваги і недоліки.
Перевага використання іменованого діапазону в даному сценарії полягає в тому, що зміна назви листа не вплине на список перевірки. nЕто підкреслює недолік функції ДВССИЛ (INDIRECT) - а саме, будь-яка зміна назви листа не буде автоматично відображатися в функції ДВССИЛ (INDIRECT), тому доведеться вручну змінити функцію, вказавши нову назву аркуша.
Перевага функції ДВССИЛ (INDIRECT): якщо з іменованого діапазону буде видалена перша осередок або рядок або останній осередок або рядок, то іменований діапазон поверне помилку #REF. У цьому недолік іменованих діапазонів - якщо видалити комірки або рядки з іменованого діапазону, ці зміни не вплинуть на список перевірки.