Изменяется путь при копировании файла в другую папку

[MSiUzaQ]

Доброго времени суток уважаемые!

У меня, значит, следующая проблема:
имеется несколько папок с файлами:

Дата => Сводная => Сводная.xls - (В папке "Дата" находятся шаблоны которые я копирую в папку "Апельсины" и переименовываю в зависимости от месяца. В папке "Сводная" - сводный файл с данными из файлов-месяцев из папки "Апельсины")

2012 => Апельсины => Июнь.xls
                                => Июль.xls

Когда файл с названием месяцев находится в папке "Дата" как шаблон формула: =ЕСЛИОШИБКА(ВПР(Y5;'Q:\data\Сводная\[Сводная.xls]Сводная'!$C$1:$Q$65536;2;0);""),
а как только я копирую файл в папку "2012" формула становится:
=ЕСЛИОШИБКА(ВПР(Y5;'Q:\2012\Апельсины\Сводная\[Сводная.xls]Сводная'!$C$1:$Q$65536;2;0);"") и при чем перестает работать ):

Не подскажите как сделать так чтобы при копировании формула не менялась?

[MSiUzaQ]

Помогите пожалуйста написать макрос который бы заменял содержимое определенных ячеек (из всего 14, но возможно будет больше) на:  =ЕСЛИОШИБКА(ВПР(Y5;'Q:\data\Сводная\[Сводная.xls]Сводная'!$C$1:$Q$65536;2;0);"").

[MSiUzaQ]

Сделал кнопку, на нее сделал макрос:

Sub SetCellFormula()
   Range("M3") = "=ЕСЛИОШИБКА(ВПР(Y5;'Q:\data\001.System\[Сводная.xls]Сводная'!$C$1:$Q$65536;2;0);"")"
End Sub

Ругаецо... говорить:

excel run-time error '1004'

Application-defined or object-defined error

[Poltava]

Запишите формулу макро редактром а потом из него скопируйте. Разницу увидите сразу и как миниму это будет формула переведенная на англиский язык

[GWolf]

Добрый вечер!

Если уж дело пошло к VBA, то предлагаю решать задачу средствами VBA, а не формулами листа.

[MSiUzaQ]

Спасибо Всем большое что откликнулись)

Запишите формулу макро редактром а потом из него скопируйте. Разницу увидите сразу и как миниму это будет формула переведенная на англиский язык

Я не знаю что такое макро редактор, пробовал искать в гугле, он не чего путного не показал подскажите пожалста в какую сторону капать?..

Если хотите использовать формулу на русском языке, используйте в коде FormulaLocal

пробовал сделать вот так:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  Sheets("Лист3").Range("M3").FormulaLocal = "=ЕСЛИОШИБКА(ВПР(Y5;'Q:\data\001.System\[Сводная.xls]Сводная'!$C$1:$Q$65536;2;0);"")"
    Application.EnableEvents = True
End Sub

но говорит что:

Run-time error 9:
Subscript out of range

Добрый вечер!

Если уж дело пошло к VBA, то предлагаю решать задачу средствами VBA, а не формулами листа.

Боюсь что до VBA полностью я еще не дорос( но все же попытаюсь посёрфить мож чаво раскопаю)

[Poltava]

Я не знаю что такое макро редактор

знать не знаю но использую 
почитайте здесь
ну а по поводу формулы попробуйте это "")" заменить на вот это """")"

[MSiUzaQ]

Я не знаю что такое макро редактор

знать не знаю но использую 
почитайте здесь
ну а по поводу формулы попробуйте это "")" заменить на вот это """")"

Вы имели введу так:

Sub SetCellFormula()
   Sheets("Лист3").Range("M3").FormulaLocal = "=ЕСЛИОШИБКА(ВПР(Y5;'Q:\data\001.System\[Сводная.xls]Сводная'!$C$1:$Q$1000;2;0);"""")"
End Sub

Все равно не работает ошибка 9...

[Poltava]

У меня ваш код работает! Вложил файл попробуйте запустить формула пишется в ячейку A1 на лист3

[ShAM]

Все равно не работает ошибка 9... 

Может, у Вас "Лист3" отсутствует?

[MSiUzaQ]

Всем спасибо огромное за участие. Проблема решилась переносом папки с шаблоном в корень диска. Теперь при копировании выдает правильный путь)

З.Ы.: Хочу еще раз поблагодарить всех кто проявил участие в моем вопросе. Вы делаете мир немного лучше)

[Poltava]

Ну не думаю что это решение проблемы это скорее ее обход! не люблю я такие решения!
1) Макрос у вас заработал ? если нет то может скиньте файл в котором он не работает. Если не работает и в моем примере то тут подумать нужно а все ли вы правильно делаете.
2) Как то просмотрел тему и не увидел очевидного решения без использования vba. даже странно что никто его вам раньше не подсказал. Если вам нужно чтоб ссылка не менялась
или менялась только так как нужно вам, то попробуйте использовать такую формулу

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

=ЕСЛИОШИБКА(ВПР(Y5;ДВССЫЛ("'Q:\data\001.System\[Сводная.xls]Сводная'!$C$1:$Q$1000");2;0);"")либо вот такую где вместо ХХХ подставьте ссылку на ячейку содержащую ссылку на нужный вам файл в виде текста

[MSiUzaQ]

Ну не думаю что это решение проблемы это скорее ее обход! не люблю я такие решения!
1) Макрос у вас заработал ? если нет то может скиньте файл в котором он не работает. Если не работает и в моем примере то тут подумать нужно а все ли вы правильно делаете.
2) Как то просмотрел тему и не увидел очевидного решения без использования vba. даже странно что никто его вам раньше не подсказал. Если вам нужно чтоб ссылка не менялась
или менялась только так как нужно вам, то попробуйте использовать такую формулу

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

=ЕСЛИОШИБКА(ВПР(Y5;ДВССЫЛ("'Q:\data\001.System\[Сводная.xls]Сводная'!$C$1:$Q$1000");2;0);"")либо вот такую где вместо ХХХ подставьте ссылку на ячейку содержащую ссылку на нужный вам файл в виде текста

Уважаемый Poltava,
Макрос заработал... у меня действительно не было Листа 3, а был Лист 1, прозевал я его) Как я понял у пользователя EXCEL 2003 поэтому формулу с "ЕСЛИОШИБКА" пришлось исключить)

[Poltava]

1)Ну во первых ЕСЛИОШИБКА это просто удобная формула для 2007 экселя в 2003 вместо нее можно использовать конструкцию из ЕОШ и ЕСЛИ формула будет более громоздкой но по сути будет делать тоже самое
2)ЕСЛИОШИБКА это я взял у вас из вашего же поста и идея не в том, а в использовании функции ДВССЫЛ именно эту часть я вам поменял.
3)Если конечный пользователь не вы я бы еще раз обратил внимание на ДВССЫЛ в отличии от макроса который может не сработать если они выключены формула сработает всегда!

[Serge 007]

...в отличии от макроса, который может не сработать если они выключены, формула сработает всегда!

Кроме тех случаев, когда выключен автопересчёт