Ошибка #REF в формуле для подтягивания данных с предыдущего листа после выходных

[Valetnina]

Здравствуйте.
В приложенном файле пример формулы, с помощью которой я подтягиваю вчерашнее значение из предыдущего листа (ячейка U12) для учета его в сегодняшнем (ячейка I5). Листы создаются на каждый день, кроме выходных. И как доходим до выходных - после пятницы 08.03 следующий лист понедельник 11.03 - появляется #REF. Существует ли способ решить это ?
Заранее спасибо за любую помощь.

[Дмитрий Щербаков(The_Prist)]

А так не подойдет?
=ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТАЗНАЧ(ПРАВБ(A1;10));-1);"ДД.ММ")&"!U12")
только для не рус.локализации может потребоваться заменить "ДД.ММ" на "DD.MM"

[Valetnina]

так пока тоже не получается, пробую и с "ДД.ММ" и с "DD.MM"
С этой формулой почему-то сразу #ИМЯ, даже до выходных, при последовательных датах (вкладка "06.03" в приложенном файле).
Ваша формула, Дмитрий, компактнее, это плюс.
В итоге есть изначальная формула

Код (vb) Выделить Развернуть

=ДВССЫЛ(ПРАВБ(0&ЛЕВБ(ПРАВБ(ЯЧЕЙКА("filename";A1);5);2)-1;2)&"."&ПРАВБ(ЯЧЕЙКА("filename";A1);2)&"!U12"),
которая работает, но до первого выходного дня,
и формула

Код (vb) Выделить Развернуть

=ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТАЗНАЧ(ПРАВБ(A1;10));-1);"ДД.ММ")&"!U12")
, которая пока дает ошибку #ИМЯ). Возможно, это я где-то ошибаюсь, адаптируя эту формулу, пока не нашла где.

[Дмитрий Щербаков(The_Prist)]

В первом сообщение в ячейке А1 был текст вида "Дата: 10.03.2019". Сейчас этого текста там в некоторых листах нет. Возможно, проблема в этом. Если добавить - все работает.
Прикладываю файлик, в котором надо будет только для функции ТЕКСТ изменить при необходимости формат.
Но ошибка #ИМЯ! обычно связана с обращением к несуществующей функции или ячейке,именованному диапазону.

[Valetnina]

Да, Дмитрий, Вы правы, кое-где не было указания на Дату в А1.
Это исправила, однако, на моем компьютере первый же лист, где значение в I5 вычисляется предложенной Вами формулой, дает результат (теперь) #VALUE или #ЗНАЧ.
Формат даты я поменяла с "ДД.ММ" на "DD.MM", и всё равно то же самое.
По оценщику формул вижу, что на первом параметре шага  "ПРАВБ(A1;10)" дата считывается как надо (10 первых знаков справа = 05.03.2019), а дальше, на параметре "10", и появляется #VALUE.
Можете ли Вы подсказать, с чем это может быть связано ?.. и что можно подправить.

[Дмитрий Щербаков(The_Prist)]

Надо посмотреть как отображается дата в статус-баре Windows.
Если формат вроде "02/04/2019" - то можно сделать так:
ПОДСТАВИТЬ(ПРАВБ(A1;10);".";"/")
Если же там что-то вроде "4/2/2019", т.е. чисто американский формат отображения даты, то все немного хуже. И надо крутить как-то так(это в принципе будет универсальный вариант):
=ДАТА(ПРАВБ(A1;4);ПСТР(ПРАВБ(A1;7);1;2);ПСТР(ПРАВБ(A1;10);1;2))

[Valetnina]

"Повертела"  .
Про второй вариант есть вот что сказать :
Да, он позволяет получить дату в виде "день/месяц/год". И эта дата представляется в формате например "43529" для даты 05/03/2019 или "43528" для даты 04/03/2019. И да, это убирает сбой, возникавший в общей формуле на шаге  (ПРАВБ((A1;10)). Ура.

Далее мне нужно заменить часть исходной формулы, в которой рождалась ошибка #VALUE, на этот новый вариант и вместо
=ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТАЗНАЧ(ПРАВБ(A1;10));-1);"DD.MM")&"!U12")
получаю
=ДВССЫЛ(ТЕКСТ(РАБДЕНЬ(ДАТА(ПРАВБ($A$1;4);ПСТР(ПРАВБ($A$1;7);1;2);ПСТР(ПРАВБ($A$1;10);1;2));-1);"DD.MM")&"!U12").
Этот новый вариант пока дает в итоге #REF и, сдается мне, дело опять в формате. Потому что финальные шаги этой формулы выглядят так :
=ДВССЫЛ(ТЕКСТ(43528;"DD.MM")&"!U12")
и
=ДВССЫЛ("DD.03!U12")
чего Excel, разумеется, принять не может, за что и выдает свое
=#REF.
В  статус-баре к сожалению не получается увидеть тип формата, поэтому и пользуюсь оценщиком формул.

Наверное, проблема опять в формате (если только я не ошиблась где-то, что допускаю, так как "верчу" уже долго и упорно). Пробовала и с DD.MM, и с ДД.ММ, и вообще без преобразования в ТЕКСТ, и без РАБДЕНЬ - не помогает.
Получается, решение лежит в другой плоскости. HELP please.

[Дмитрий Щербаков(The_Prist)]

=ДВССЫЛ("DD.03!U12")

Насторожить должно именно то, что там DD в итоге. Надо просто назначить любой ячейке с реальной датой нужный формат и посмотреть как он выглядит. Может там не ДД и не DD должно быть, а что-то еще(от локализации зависит).

вообще без преобразования в ТЕКСТ, и без РАБДЕНЬ

ну без них-то работать и не будет. Поэтому крутить надо все же формат. Либо сильно все усложнять. Вроде этого:
=ДВССЫЛ(ТЕКСТ(День(РАБДЕНЬ(ДАТА(ПРАВБ($A$1;4);ПСТР(ПРАВБ($A$1;7);1;2);ПСТР(ПРАВБ($A$1;10);1;2));-1));"00")&"."&
ТЕКСТ(Месяц(РАБДЕНЬ(ДАТА(ПРАВБ($A$1;4);ПСТР(ПРАВБ($A$1;7);1;2);ПСТР(ПРАВБ($A$1;10);1;2));-1));"00")&"!U12")

[Valetnina]

Спасибо, буду пробовать еще.
Всё же тот факт, что я так и привязана к имени листа, и что присутствует "-1", чтобы попасть на день назад, вызывает опасение, что в итоге, после выходных, когда недостаточно вернуться лишь на 1 день назад, формула так и не заработает...

[Valetnina]

Дима, а что значит фраза "от локализации зависит" ? от локализации ячейки с датой ?

[Дмитрий Щербаков(The_Prist)]

привязана к имени листа, и что присутствует "-1"

-1 привязана к РАБДЕНЬ, которая выходные как раз не учитывает.

что значит фраза "от локализации зависит"

Локализация ПК. Т.е. формат даты именно на ПК, независимо от формата ячейки. Функция ТЕКСТ к ней чувствительна.

Если не хочется привязываться к именами листов и надо всегда смотреть на предыдущий лист(независимо от его имени), то только VBA. Вроде такой функции:

Код (vb) Выделить Развернуть

Function GetPrevSheetValue(CellAddr As String)
   Application.Volatile True
   Dim wsCur As Worksheet
   Set wsCur = Application.Caller.Parent
   If wsCur.Index = 1 Then
       GetPrevSheetValue = ""
   Else
       GetPrevSheetValue = wsCur.Parent.Worksheets(wsCur.Index - 1).Range(CellAddr).Value
   End If
End Function
использовать на листе просто указав адрес ячейки, значение которой надо получить из предыдущего листа:
=GetPrevSheetValue("A1")

[Valetnina]

Да, спасибо огромное !  Я тоже потратила менее 5 минут на решение этой задачки с помощью VBA. Как жаль, что иногда надо обходиться без него .

[Valetnina]

Здравствуйте.
Попробовала проверить "локализацию" своего компьютера в части даты с помощью формул "=СЦЕП($C$1;"*";ТЕКСТ($A$1;"ДД.ММ.ГГГГ"))" и "=СЦЕП($C$1;"*";ТЕКСТ($A$1;"DD.MM.AAAA"))", обе дают одинаковый корректный результат, что дает мне право думать, что проблема была не в формате.
В любом случае, вот формула, которая работает и решает, в том числе, и проблему "выходных" :
=ДВССЫЛ(ТЕКСТ(ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2)));ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2)))+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2));2)<>1;-1;-3)));"00")&"."&ТЕКСТ(МЕСЯЦ(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2)));ДЕНЬ(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2)))+ЕСЛИ(ДЕНЬНЕД(ДАТА(ГОД(СЕГОДНЯ());ПРАВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2);ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename";$A$1);ДЛСТР(ЯЧЕЙКА("filename";$A$1))-НАЙТИ("]";ЯЧЕЙКА("filename";$A$1)));2));2)<>1;-1;-3)));"00")&"!"&"$U$12")

Единственным ее слабым местом, помимо громоздкости, считаю ГОД, так как неизвестно, как поведет себя она например в следующем году. Доживем - увидим.
Всем удачи и вдохновения
И еще раз спасибо.