КОСВЕННАЯ функция для ссылки на диапазоны ячеек других листов

Я изо всех сил пытаюсь найти способ сделать динамические КОСВЕННЫЕ ссылки на диапазоны ячеек на других листах. Буду признателен за любые предложения, подробности:

Рабочая тетрадь включает 4 рабочих листа (Продукт1, Продукт2, Продукт3, Склады). Лист Склады содержит следующую формулу для заполнения инвентарного списка для каждого склада на основе трех рабочих листов продуктов (получено из http://exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/). Это формула в ячейке B3:

=IFERROR(INDEX(INDIRECT(B$2&"!B$3:B$400"),SMALL(IF(INDIRECT(B$2&"!$C$3:$C$400")=$B$1,ROW(INDIRECT(B$2&"!B$3:B$400"))-ROW(INDIRECT(B$2&"!B$3"))+1),ROWS(Product1!$B$3:$B3))),"")

Где:

Склады -> $ B $ 1 = Склад1 или Склад2

Склады -> B2, C2, D2 = Заголовки столбцов для Product1, Product2, Product3

Таблицы продуктов -> Столбец B = Серийный номер

Таблицы продуктов -> Столбец C = Местоположение (Склад1, Склад2)

В настоящее время мне нужно изменить последнюю часть формулы для каждой строки: ROWS(Product1!$B$3:$B3), ROWS(Product2!$B$3:$B3), ROWS(Product3!$B$3:$B3). Я пытаюсь динамически связать ее с заголовком столбца, как и другие части кода (например, ROW(INDIRECT(B$2&"!B$3:B$400")). Я застрял, потому что диапазон $B3 должен изменяться с каждой строкой, тогда как остальные являются статичными и хорошо заключены в кавычки.

Это важно, потому что я хочу, чтобы менее способные пользователи могли копировать формулу в новые столбцы без необходимости вносить в нее поправки. Ценю любые мысли по этому поводу!


excel excel-indirect
person Joe    schedule 03.04.2015    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Есть несколько способов ввести увеличивающееся число в конкатенация строк функции INDIRECT. Я предпочитаю простую функцию СТРОКА как ROW(1:1), который при заполнении возвращает 1, 2, 3, .... В вашем случае k yu будет начинаться с ROW(3:3).

... -ROW(INDIRECT(B$2&"!B"&ROW(3:3)))+1),
..., ROWS(INDIRECT(B$2&"!B3:B"&ROW(3:3))),"")

Просто напоминание; оставление абсолютных обозначений $ на адресах ячеек, которые описаны с текстом, может быть полезно в качестве визуального напоминания о том, что может перейти к следующему (относительному) или нет (абсолютному), но текст, представляющий строку или столбец, никогда фактически изменится, так что они, по крайней мере, немного излишни.

person Community    schedule 04.04.2015
comment
Спасибо за ответ Jeeped. Мне нужна функция столбца, а не строка. Мне нужно изменить последнюю часть формулы для каждого столбца, поскольку ROWS(Product1!... жестко запрограммирован в формуле. Я надеюсь, что есть способ связать имя листа со значениями в строке 3 листа «Товары», чтобы не менять формулу с ROWS(Product1!... на ROWS(Product2!... и т. Д. Я понимаю, что это сложно объяснить, поэтому я связал к примеру в Google Таблицах ( - person Joe; 04.04.2015
comment
Не обращайте внимания на меня, Jeeped, я изначально неправильно воспользовался вашим предложением, но после некоторой игры осознал его великолепие. Огромное спасибо! Это было в моем списке дел уже довольно давно. - person Joe; 04.04.2015

arrow_upward
0
arrow_downward

Вы можете изменить формулу INDIRECT(), чтобы учесть номер строки текущей ячейки, например:

=INDIRECT(B$2&"!B$"&ROW()&":B$"&(ROW()+397))

В качестве альтернативы вы можете использовать функцию OFFSET() для перемещения ссылки с каждой строкой:

=OFFSET(INDIRECT(B$2&"!B$3:B$400"),ROW()-3,0)
person ttaaoossuuuu    schedule 03.04.2015
comment
Приносим извинения за недоразумение, Taosique. Мне нужно изменить ссылку на диапазон в других листах ROWS (SHEETNAME! $ B $ 3: $ B3), а не ссылки на диапазоны в листе, содержащем формулу. Выполнение чего-то вроде ROWS (INDIRECT (B $ 2 &! & $ B $ 3: $ B3)) указывает на диапазон $ B $ 3: $ B3 на текущем листе, а не на лист, указанный в B2. Имеет ли это смысл? - person Joe; 03.04.2015