Динамически ссылаться на рабочие листы Excel

У меня есть проблема, которая должна быть такой простой, но я не понимаю ее. Я распечатываю прайс-листы для магазина, и в этом году они разделили номера деталей на 5 рабочих листов вместо одного.

Когда пользователь хочет распечатать ценник, он вводит = в C10, щелкает рабочий лист «Прайс-лист» и переходит к нужному номеру детали.

Результирующая формула для C10: =Pricelist!B40

E10 должен содержать больше информации о номере детали, поэтому формула E10: =VLOOKUP(C10,Pricelist!B:N,2,FALSE)

Однако теперь с новыми рабочими листами она может выбрать Рабочий лист «Новые_Элементы», и в этом случае результирующая формула для C10 будет следующей: =Новые_Элементы!B40

Как я могу написать формулу для E10, чтобы она ссылалась на тот же рабочий лист, что и C10.

Мне нужно E10 =VLOOKUP(C10,Прайслист!B:N,2,FALSE)

автоматически становится *=VLOOKUP(C10,New_Items!B:N,2,FALSE)*

Это имеет смысл? Возможно ли, чтобы Excel изменил ссылку на рабочий лист на основе ссылки на другую ячейку?

ТИА!


excel-formula excel-2007
person Thinkwell    schedule 19.08.2011    source источник

Ответы (3)


arrow_upward
13
arrow_downward

Да, вы можете сделать это, используя ДВССЫЛ(). Это может занять немного времени, так как вам нужно как-то определить имя листа.

=VLOOKUP(C10, INDIRECT( Concatenate(cell-with-sheet-name, "!B:N"),TRUE ) ,2,FALSE)
person Eddy    schedule 20.08.2011
comment
Вам может потребоваться выполнить дополнительную конкатенацию строк в других ячейках, чтобы определить фактическое значение для ячейки с именем листа. Это также можно упростить, сделав все листы поиска как можно более похожими. - person smaclell; 20.08.2011
comment
... нужно как-то определить имя листа. Да, мне нужно выяснить, как извлечь имя листа из C10 и сделать так, чтобы формула E10 ссылалась на тот же лист. Может ли INDIRECT помочь мне с этим? Кроме того, данные на всех листах структурированы одинаково, поэтому, преодолев это препятствие, я свободен от дома. - person Thinkwell; 20.08.2011
comment
Если vba включена, простая функция может использовать Cell.Formula, чтобы проанализировать имя листа и вернуть его. Краткое руководство по работе с UDF можно найти на ozgrid.com/VBA/ExcelIsFormula.htm. - person Eddy; 20.08.2011

arrow_upward
1
arrow_downward

Можете ли вы использовать vba?

Если это так, попробуйте этот простой udf

Function MyLookup(ref As Range, Offset as Long) As Variant
    MyLookup = Range(ref.Formula).Offset(0, Offset)
End Function

Ячейка E10 =MyLookup(C10, 1)

person chris neilsen    schedule 20.08.2011

arrow_upward
1
arrow_downward

Вы можете создать VBA UDF

Function GetShtNm(rng As Range) As String
    Application.Volatile

    If InStr(1, rng.Formula, "!") = 0 Then
        GetShtNm = vbNullString
    Else
        GetShtNm = Mid$(rng.Formula, 2, InStr(1, rng.Formula, "!") - 2)
    End If
End Function

и используйте эту формулу с косвенным, предоставленным Эдди.

Эта пользовательская функция позволит вам динамически отслеживать изменения имени листа в рамках функции VLOOKUP INDIRECT.

person RonnieDickson    schedule 29.08.2011