Страницы: 1
RSS
Как создать гиперссылку формулой поиска значения для перехода на нужную ячейку?
 
Господа, помогите пожалуйста с такой задачей:

На одном листе для каждого клиента создаю в столбик новые "таблицы-карточки", и нумерую их.
А в самом верху создаю сводную таблицу всех этих "карточек", из которой должна быть возможность переходить на нужную мне "карточку". Мне нужно, чтобы в сводной таблице работали "гиперссылки-формулы" на каждую из ниже созданных и пронумерованных карточек. Конечно же это можно делать вручную: выбрал ячейку, нажал гиперссылку, выбрал вторую ячейку для перехода на неё, и гиперссылка готова. Но это лишняя работа. Хочу прописать сразу все гиперссылки в столбик, чтобы каждая из них автоматом находили свои номера карточек (1, 2, 3 и т.д.) в конкретном столбике для нумерации, как только появляется новая "табличка-карточка" со своим номером.

Файл прикрепил.
В красной ячейке - номера карточек, которые должна искать формула в гиперссылке.
В зелёной ячейке - вручную созданная гиперссылка.
В жёлтой ячейке - моя попытка создать гиперссылку-формулу, которая не работает.

Подскажите пожалуйста, что не так с формулой?
Код
=ГИПЕРССЫЛКА(АДРЕС(ПОИСКПОЗ("2";$A$1:$A;0);1;"ИНТ. '21");"►")
Изменено: vikttur - 26.06.2021 00:40:17
 
Как-то так:
Код
=HYPERLINK(CONCATENATE("#";"'ИНТ. 21'!";ADDRESS(MATCH($A2;$A$17:$A$90;0)+ROW($A$17);1;3));"►")

Или так, если нужно выделить всю таблицу:
Код
=HYPERLINK(CONCATENATE("#";"'ИНТ. 21'!";(ADDRESS(MATCH($A2;$A$17:$A$90;0)+ROW($A$17);1;3)&":"&ADDRESS(MATCH($A2;$A$17:$A$90;0)+ROW($A$26);8;3)));"►")

В файле оба варианта.
Изменено: memo - 17.06.2021 01:30:49 (Дополнение)
 
memo, спасибо, что откликнулись!

А если мне нужно, чтобы в сводной таблице изначально не было нумерации, то есть, чтобы ссылка находила номер, когда он появится вместе с новой карточкой ниже. Нумерация добавляется другим путём. То есть мне нужно, чтобы формула не была связана с нумерацией в сводной таблице, а сама находила номер в карточках ниже. То есть, в моём понимании, где-то в формулах должны отображаться номера в кавычках "1", в следующей формуле "2", и т.д. Можете скорректировать, пожалуйста?

И интересует кусок первой формулы "+ROW($A$17)" , что он даёт?
 
Цитата
Антон Петровский написал:
чтобы формула не была связана с нумерацией в сводной таблице, а сама находила номер в карточках ниже.
Это совсем несложно, изменил, теперь номера для формулы будет создавать эта простая формула ROWS($A$2:A2), но я бы рекомендовал добавить ее и к треугольникам, чтобы легче было ориентироваться. Сделал в примере, если не нужно, то из этого куска ROWS($A$2:A2)&" ►" просто уберите ROWS($A$2:A2)&
Цитата
Антон Петровский написал:
"+ROW($A$17)" , что он даёт?
Поскольку в ячейках А2:А7 была нумерация, то диапазон поиска для функции ПОИСКПОЗ пришлось сместить до начала строки с карточками. Ну и соответственно требовалась поправка на 17 строк, чтобы формула работала корректно (т.е. находила номера не в ячейках А2:А7, а в самом массиве). В новом варианте это не требуется, поскольку диапазоном задан весь столбец.
Изменено: memo - 17.06.2021 03:26:08
 
memo, спасибо вам большое, только прошу ещё немножко докрутить формулу. Мне нужно, чтобы в формуле был конкретный параметр "1", "2", "3". Чтобы я смог покопировать в столбик формулу, и в каждой формуле поменять только эти параметры "1", "2", "3". Возможно это будет не просто цифры, а допустим "S1", "S2", "S3" и т.д., и формула должна находить именно эти значения, это мне очень важно.

Признаюсь, мне это нужно для Google Sheets, но я не нахожу форумов сугубо по гугл таблицам, и большинство формул работают одинаково в обоих сервисах, потому пишу на этом форуме.

Если сможете осилить это в гугл таблице, то буду премного благодарен!

https://docs.google.com/spreadsheets/d/1DEdLCypKnWW0GoLB8NpRRt6-5VHXY1GJDFAV4bRGXxE/edit?usp=sharing
Изменено: Антон Петровский - 17.06.2021 13:58:16
 
В MATCH замените ROWS($A$2:A4) на 1, 2, 3 и т.д. Если в карточках будет текст типа S1, S2, S3 тоже самое пропишите и в MATCH, только в кавычках.
Кстати, в Google Sheets ГИПЕРЛИНК явно работает по другому, даже если загрузить туда уже готовый файл, то ошибка синтаксиса хоть и не выдается, но функция не работает.
Изменено: memo - 17.06.2021 14:28:32
 
memo,
попытался сформировать ссылку вот так:
Код
=ГИПЕРССЫЛКА("'ИНТ.  21'!"&АДРЕС(ПОИСКПОЗ("S3";A1:A;0);1;4);" ►")

'ИНТ. 21'!A42

вместо сцепки использовал &, он в гугле работает как сцепка. И вроде значение формируется, как надо для гиперссылки, а всё-равно не срабатывает почему-то.
Отдельно ПОИСКПОЗ, работает, находит нужную строку,  АДРЕС тоже правильно формирует номер ячейки, а гиперссылка не срабатывает...
Изменено: vikttur - 26.06.2021 00:40:41
 
Даже попробовал максимально упростить для эксперимента:
Код
=ГИПЕРССЫЛКА("'ИНТ.  21'!"&"A42")

Тоже получается 'ИНТ. 21'!A42
Но ячейка не превращается в гиперссылку...
Изменено: vikttur - 26.06.2021 00:41:04
 
Антон Петровский,
Честно говоря, без понятия. Как я уже писал, функция явно работает по-другому. Может имеет смысл обратиться на какой-нибудь буржуйский форум заточенный именно под Sheets.
 
memo, Понял, вижу, по логике вроде всё верно, а функция работает иначе. В любом случае, спасибо вам за ваше время и попытки!
 
Спасибо камраду Kashimirush с миры, где он выложил решение. Честно говоря, поначалу я не вполне въехал, но смесь красного абсента и koskenkorva дала нужные результаты.
Итак, клацаем правой клавишей мышки по любой ячейке диапазона поиска, выбираем Get Link to this Cell и вставляем его в какую нибудь ячейку, скажем E2. Из полученного длинного линка в конце убираем номер ячейки (оставляем только А в конце) и получаем массив (шаблон), который оказывается необходим для критерия поиска.
И затем уже формируем формулу, которая будет выглядеть так:
Код
=HYPERLINK($E$2&MATCH("S1";$A:$A;0);"►")

Код
=HYPERLINK($E$2&MATCH("S2";$A:$A;0);"►")

и т.д.
Кликаем на сформированный линк, и в появившемся маленьком окошке видим, что формула правильно определила название листа и четко указывает на нужную ячейку. Кликаем на нее и как говорил Доджерс: "That's our destination planet".
 
memo, Свершилось!!! Я понял, формуле нужна была ссылка на лист! Отличное решение!

Большое спасибо вам, memo,  и спасибо Kashimirush !!!
Страницы: 1
Наверх