Вот два варианта:
- В сводной рабочей таблице используйте функцию
INDIRECT
в сочетании с таблицей пути, рабочей книгой, таблицей и именем ячейки суммы для каждого отдела. Функция INDIRECT возвращает ссылку, указанную в строке: например, если ячейка A1 содержит строку «Sheet1!D19 ", тогда = INDIRECT(A1) даст значение в ячейке D19 Sheet1.
Итак, вам нужен набор ячеек в сводной таблице, который содержит ссылки на ячейку или ячейки в каждой рабочей книге отдела, которые суммируют штатное расписание для отдела. Таблица для создания этих ссылок и прилагаемые формулы могут выглядеть следующим образом.
Формулы для штатного расписания каждого отдела, показанные в столбце F рисунка, оценивают как действительные ссылки на ячейки в ведомственных рабочих книгах. Я выбрал построение формул из ссылок в таблице, чтобы обеспечить гибкость - необходимо изменять только записи таблицы, если, например, изменяется список рабочих книг, которые необходимо включить, а не сами формулы.
Эти формулы будут обновляться только тогда, когда рабочие книги, на которые они ссылаются, открыты; если рабочая книга отдела закрыта, то в общей сводной таблице для этого отдела в сводной ведомости будет #REF! ошибка при пересчете значения. Другими словами, вы не можете извлечь данные из закрытой книги.
- Используйте функцию или подпрограмму VBA, которая может извлекать данные из закрытой рабочей книги.
Существует несколько различных подходов к этому, в том числе эта функция, с веб-сайта Джона Уокенбаха, который работает только при использовании в процедуре VBA, и этот метод можно использовать непосредственно на рабочем листе. Я проверил оба, и они работают как заявлено.