Доброго времени суток.
Хотелось бы, чтобы из листа "Планы" в лист "Текущие задачи" копировалась информация "Адрес" и "ID трекера" в первую пустую ячейку побригадно. То есть например, в строках 9, 10 должны быть формулы.
Например: Планирую на бригаду Назарова и Усольцева на 4 декабря на листе "Планы" работу по адресу: Юмашева 22а с ID трекером этой задачи СВЛ-2878417. Как только я ввел эти данные, хотелось бы, чтобы эти данные продублировались в листе "Текущие задачи" в первых свободных ячейках также под эту бригаду (строки 9 и 10).
Ну и верх пилотажа - как только они вводились не в ручную, а формулой переноса, то статус в ячейке бы сменился на "В планах"
Если это возможно без макроса, я буду рад. Если нельзя, то тогда не рассматривайте этот вариант. Политика компании не даст запустить макрос.
Знаю, что есть функция ИНДЕКС И СМЕЩПОЗ. Но сколько ни читал, сколько не пытался - у меня не заводится.
Ниже выкладываю файл-пример.
[вложение удалено администратором]
Значит в строках 9 и 10 не могут быть формулы, т.к. одновременно в ячейке нельзя использовать формулы и чтобы юзер печатал в ячейке. Когда юзер будет печатать в ячейке, то формула будет удаляться.
Значит остаётся только использование макросов.
Хорошо. Если пользователь не будет вводить туда данные, а данные будут формироваться исходя из листа "Планы"?
Сначала планирование, затем пользователь продвигает задачу по всем статусам, без внесения изменений в строки 9 и 10.
Тогда возможно запилить формулой? Конечный итог ввода зависит от моего решения и я вправе указать, что заполнять, а что будет осуществляться формулой (на случай защиты от дурака - есть запрет ввода данных в ячейку).
Согласно заданию из первого сообщения, лист "Текущие задачи" должен выглядеть так (смотрите вложенный файл), а у вас в первом сообщении лист выглядит по-другому.
[вложение удалено администратором]
Вложение, которое вы сейчас вставили, является верным. Строки "Адрес" и "ID трекера" в листе "Планы" 5 и 8, а в "Текущие задачи" 9 и 10.
Но нужно помнить, если юзер изменит данные на листе "Планы", то на листе "Текущие задачи" останутся данные, введённые юзером вручную. Например, если юзер вручную печатает в строке 11.
Если он изменит данные в листе план скажем 4 декабря, они не внесутся в новую свободную ячейку в текущих задачах, а изменятся в той, которая была свободной при первом вводе в блок 4 декабря?
В любом случае, мне придётся контролировать эти изменения. Буду иметь ввиду.
Может быть есть какое-то условное форматирование при вводе новых данных в ячейку - чтобы подсвечивалось другим цветом?
Цитата:
Если он изменит данные в листе план скажем 4 декабря, они не внесутся в новую свободную ячейку в текущих задачах, а изменятся в той, которая была свободной при первом вводе в блок 4 декабря?
Если смотреть файл из первого сообщения, то да: в новую пустую ячейку данные не будут записаны, а будут изменены в столбце J.
Цитата:
Может быть есть какое-то условное форматирование при вводе новых данных в ячейку - чтобы подсвечивалось другим цветом?
Не знаю, как такое сделать средствами экселя. Можно попробовать сделать с помощью макроса.
Я понял. Буду после первого ввода в планах блокировать ячейки. Потом изменения будут только через меня. Такое бывает не часто - так что вполне себе пойдёт.
Так что готов потестировать Вашу формулу, а заодно и для себя усвоить ее алгоритм для возможного применения в будущем в других решениях.
Формулы пока нет и не знаю, смогу ли сделать.
В течение дня посмотрю.
Разве ИНДЕКС И СМЕЩПОЗ в умелых руках, как ваши, не оружие?
ID трекера - это уникальные данные? Или один и тот же id может быть несколько раз в строке 8?
Уникальные данные. На каждую новую задачу свой ID, по которому потом в программе идентифицируется задача. Копируется из проги гиперссылкой.
Сейчас не решение вашей задачи, просто рекомендация.
Если в файле много формул, то используйте формат xlsb. В этом формате файлы будут меньшего размера и файл будет быстрее работать.
Спасибо. Попробую пересохранить в этом формате.
В файле формулы вставлены до столбца V. Скопируйте формулы в остальные столбцы.
Как копировать формулы в остальные строки:
выделите F9:DV9 - скопируйте - выделите ячейку "F14" - вкладка "Главная" - кнопка-стрелка Вставить - Формулы.
То же и для ID трекеров.
На обоих листах ФИО должны быть в такой же последовательности.
Кол-во строк в каждой группе должно быть одинаково. Пример группы: лист "Текущее задание" - строки 7 - 11.
Внутри одной строки трекеры должны быть уникальными - формула ищет нужный столбец по трекеру.
Используется имя, чтобы уменьшить формулы.
В столбце F, у трекера другая формула, чем у остальных трекеров.
Я хотел сначала использовать формулу массива, но она не работает в объединённых ячейках. Пришла идея искать трекеры.
[вложение удалено администратором]
Спасибо большое. Все работает.
Вопрос о смене статуса в Текущих задачах на "В планах" при переносе из листа "Планы" формулой не решить?
Одновременно нельзя, чтобы в ячейке была и формула и чтобы юзер печатал. На листе "Текущие задачи" юзер должен изменять статус? Если нет, то вы можете сами сделать формулу по аналогии формулы, которая вставляет адрес.
Понял. Спасибо.
Сейчас обратил внимание, как упростить формулу для адреса. Удалите из формулы имя листа "Текущие задачи":
=ЕСЛИОШИБКА(ИНДЕКС(Планы!10:10;1;ПОИСКПОЗ(F15;Планы!13:13;0));"")
Не удаляйте. Позже попробую.
Можете объяснить алгоритм формулы
=ЕСЛИОШИБКА(ИНДЕКС(Планы!10:10;1;ПОИСКПОЗ(F15;Планы!13:13;0));"")
и
=ЕСЛИОШИБКА(ИНДЕКС(Планы!C8:AG8;1;ПОИСКПОЗ("*";Планы!C8:AG8;0));"")
=ЕСЛИОШИБКА(ИНДЕКС(Планы!10:10;1;ПОИСКПОЗ(F15;Планы!13:13;0));"")
ПОИСКПОЗ(F15;Планы!13:13;0)
Поиск на листе "Планы" в строке 13 трекера из ячейки F15. Будет возвращён номер столбца, где находится трекер.
ИНДЕКС(Планы!10:10;1;номер_столбца_из_плана)
Возвращает данные из листа "Планы", из строки 10, из найденного столбца.
ЕСЛИОШИБКА
Скрывает ошибку. Ошибка происходит, если в F15 пусто.
=ЕСЛИОШИБКА(ИНДЕКС(Планы!C8:AG8;1;ПОИСКПОЗ("*";Планы!C8:AG8;0));"")
ПОИСКПОЗ("*";Планы!C8:AG8;0)
Поиск на листе "Планы" во фрагменте "C8:AG8" первой ячейки, в которой есть текст (именно текст, числа не учитываются). Возвращает порядковый номер столбца во фрагменте "C8:AG8" (именно для фрагмента, а не для всей строки).
ИНДЕКС(Планы!C8:AG8;1;найденный_номен_столбца)
Возвращает данные из листа "Планы", из фрагмента "C8:AG8", из найденного столбца.
ЕСЛИОШИБКА
Скрывает ошибку. Ошибка происходит, если на листе "Планы", во фрагменте "C8:AG8" нет текста.
Я уже вам писал, может вы не обратили внимание, но в J10 другая формула, чем в F10.
Я помню про разные формулы, но пока не дошел до j10 в освоении и разборе полетов.