Добрый, день, уважаемые формучане! Подскажите, пожалуйста, можно ли как-то перейти по гиперссылке к листу в книге, когда я ввожу формулу = Суть: на заглавном листе есть список книг в библиотеке; ячейка с наименованием каждой из книг также является гиперссылкой на отдельный лист для данной книги (с подробными данными), в соседнем столбце "количество зарегистрированных книг" я хочу ввести ссылку на ячейку в листе этой книги, где отображается эта информация. Делаю это с помощью формулы = и кликаю на нужную ячейку на нужном листе.
Проблема в том, что база книг уже очень большая (около 500 листов), трудно искать нужный лист вручную. Как можно оптимизировать процесс? Можно ли как-то вводя формулу = перейти по гиперссылке к нужному листу (когда я пытаюсь это сделать, естественно, ячейка с гиперссылкой просто вставляется в формулу).
Иван, спасибо за совет, мне эта информация пригодится при создании оглавления. Но я не нашла там ответа на свой основной вопрос. Мне нужно на главный лист вывести информацию с другого листа из определенной ячейки, как это сделать, когда очень много листов и перелистывать их вручную долго и трудно.
MarNik, а у Вас шаблоны листов по книгам одинаковые? Если да, то макросом можно так:
Код
Sub makeLinks()
Application.ScreenUpdating = False
Dim ws As Worksheet, contentWs As Worksheet, curPos As Range, ccol As Range, rrow As Range
' Лист с оглавлением
Set contentWs = ActiveWorkbook.Sheets("Оглавление")
' Очистим содержимое после шапки
Range(contentWs.Range("A3"), contentWs.UsedRange.SpecialCells(xlCellTypeLastCell)).Value = ""
' Удалим существующие на листе ссылки
contentWs.Hyperlinks.Delete
' Пройдемся циклом по всем листам
For Each ws In ActiveWorkbook.Sheets
' Нас интересуют все листы кроме листа оглавления
If ws.Name <> contentWs.Name Then
' На листе ищем значения
' Столбец = "зарегистр."
' Строка = "Итого:"
Set ccol = ws.Cells.Find(What:="зарегистр.", LookAt:=xlWhole)
Set rrow = ws.Cells.Find(What:="Итого:", LookAt:=xlWhole)
' Если оба значения есть на листе - значит на их пересечении нужное значение, добавим ссылку и значение в оглавление
If Not ccol Is Nothing And Not rrow Is Nothing Then
' Первый пустая строка в оглавлении
Set curPos = contentWs.Cells(contentWs.Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
' Столбец A = Ссылка на первую ячейку просматриваемого листа, оттуда же текст ссылки
contentWs.Hyperlinks.Add Anchor:=curPos, Address:=addrTxt, TextToDisplay:=ws.Range("A1").Value
' Столбец B = Значение на пересечении "зарегистр." и "Итого:" просматриваемого листа
curPos.Offset(0, 1).Formula = "=" & Intersect(ccol.EntireColumn, rrow.EntireRow).Address(True, True, xlA1, True)
End If
End If
Next ws
Application.ScreenUpdating = True
End Sub
Нет, не получается: 1. У меня в таблицах на листах разное количество строк, поэтому адрес нужной итоговой ячейки может быть разный. Т.е. для каждого наименования нужно будет проверять, на какой строке находится сумма зарегистрированных книг. Не очень рационально, но можно. 2. Наименования книг длинные (и они должны быть полными в оглавлении), а наименования листов могут содержать сокращенные названия. Придумала ввести еще один столбец с сокращенным названием и ссылаться на него. Но почему-то функция ДВССЫЛ не работает, дает мертвую ссылку. Вроде все делаю как в видео.
Function Количество_зарег(ByVal rCell As Range) As Double
Dim s As String
If rCell.Hyperlinks.Count <> 0 Then
s = Mid(rCell.Hyperlinks(1).SubAddress, 2, InStr(1, rCell.Hyperlinks(1).SubAddress, "!") - 3)
If s <> "" Then
Set FR = Sheets(s).Cells.Find("Итого:")
If Not FR Is Nothing Then Количество_зарег = FR.Offset(, 4).Value
End If
End If
End Function
Прописывая аргументы для ДВССЫЛ, не забывайте о правильном синтаксисе... V, намекнул Вам "где собака зарыта"... Во воложении файл с исправленной формулой.
iMrTidy, я не против макросов, я только плохо в них разбираюсь. Но вот в примере, который Вы прикрепили, это работает, если не добавляются строки на адресном листе. А у меня они будут добавляться с каждым новым поступлением. Или я неправильно поняла работу макроса.
MarNik, мой макрос представляет собой пользовательскую функцию, которая извлекает часть гиперссылки, которая, в свою очередь, может быть использована для динамического нахождения нужной ячейки. Я обновил формулу, теперь она возвращает последнюю ячейку с данными в колонке E, а адрес листа берется из соседней ячейки с гиперссылкой. Это конечно, если данные всегда в колонке E, или другой заранее известной.