Створити простий список, що випадає в осередку з даними з цього ж файлу - просто. Все ускладнюється, коли сам список і дані для нього знаходяться в різних файлах. Для вирішення цієї проблеми є кілька способів.
Спосіб 1. Функція ДВССИЛ
У простому випадку можна використовувати функцію ДВССИЛ (INDIRECT), щоб сформувати правильну посилання на зовнішній файл. Наприклад, якщо необхідно створити список, що випадає з вмістом осередків А1: А10 з листа Список з файлу Товари.xls, потрібно відкрити вікно перевірки даних через вкладку Дані - Перевірка даних (Data - Validation) і в полі Джерело (Source) ввести наступну конструкцію: = ДВССИЛ ( "[Товари.xls] Список! $ A $ 1: $ A $ 10").
Щоб сформувати правильну посилання на зовнішній файл можна використовувати функцію ДВССИЛ
Якщо файл з вихідними даними для списку лежить в іншій папці, необхідно вказати повний шлях до файлу, наприклад, наступним чином: = ДВССИЛ ( " 'C: \ Постачальники \ [Товари.xls] Список'! $ A $ 1: $ A $ 10" ). В даному випадку не забудьте укласти в апострофи повний шлях до файлу та ім'я аркуша. Мінус цього способу тільки один - список, що випадає буде коректно працювати тільки в тому випадку, якщо файл Товари.xls відкритий.
Спосіб 2. Імпорт даних
Суть цього способу в тому, що дані для списку буду імпортуватися з іншого файлу в поточний. Для цього необхідно створити оновлювану зв'язок між двома файлами.
Спочатку відкрийте файл-джерело, де знаходяться еталонні значення для списку (назвемо його, припустимо, Справочнік.xlsx). Виділіть діапазон з даними для списку і відформатуйте його як таблицю за допомогою кнопки Форматувати як таблицю на вкладці Основне (Home - Format as Table). Зверніть увагу, що у такий таблиці попередньо повинна бути зроблена «шапка» - рядок заголовка. Після цього файл Довідник можна зберегти і закрити.
Тепер відкриємо книгу, де ми хочемо створити список, що випадає (умовно назвемо її Бланк.xlsx). Вставимо чистий аркуш (Alt + F11), виберемо на вкладці Дані - Існуючі підключення - Знайти інші (Data - Existing Connections - Browse for more) і вкажемо наш файл Справочнік.xlsx. З'явиться діалогове вікно, в якому Excel запитає нас про те, яку саме таблицю ми хочемо імпортувати (якщо їх у файлі було кілька).
Тепер відкриємо книгу, де ми хочемо створити список, що випадає
Після натискання на ОК з'явиться ще одне останнє вікно, де можна вказати зручну осередок для імпорту і, натиснувши на кнопку Властивості (Properties), задати частоту оновлення інформації.
Після натискання на ОК з'явиться ще одне останнє вікно
Тут можна включити прапорець Оновити при відкритті файлу (Refresh on open), щоб кожен раз при відкритті цієї книги мати останню версію списку.
Можна включити прапорець Оновити при відкритті файлу
Після натискання на кнопку ОК, Excel завантажить дані зі створеної таблиці з файлу Довідник в наш поточний файл Бланк і отформатирует їх у вигляді таблиці.
Excel завантажить дані зі створеної таблиці
Якщо виділити імпортовану список (діапазон А2: А7 в нашому випадку), то в рядку формул можна побачити його ім'я, яке він автоматично отримує при вставці.
У рядку формул можна побачити ім'я імпортованого списку
Це ім'я також можна побачити в диспетчері імен на вкладці Формули (Formulas - Name Manager).
Залишилося створити список, що випадає, який буде посилатися на ці дані. Для цього:
- Виділяємо комірки, де хочемо створити списки, що випадають.
- На вкладці Дані тиснемо на кнопку Перевірка даних (Data - Validation).
- Вибираємо в списку дозволених типів даних варіант Список (List) і вводимо в поле Джерело (Source) наступну формулу: = ДВССИЛ ( "Табліца_Справочнік"). В англомовній версії Excel це буде = INDIRECT ( "Табліца_Справочнік").
Залишилося створити список, що випадає
Логічно було б ввести просто ім'я нашого діапазону, але, на жаль, Microsoft Excel чомусь не сприймає імена таблиць в поле Джерело. Тому ми використовуємо тактичну хитрість - функцію ДВССИЛ (INDIRECT). яка перетворює свій аргумент (ім'я нашої таблиці) в робочу посилання.
Усе. Після натискання на ОК наш список почне працювати і буде в подальшому автоматично оновлюватися, довантажуючи дані з іншого файлу.
Після натискання на ОК список почне працювати і автоматично оновлюватися