Excel Mirroring Data

M

mickp

Im not sure this is possible, but im sure that this the place to tellin
whether it is. I am trying to create a tasklist for IT team. I a
creating a Tasklist on the first worksheet, this will show all th
tasks that need completing. Then on the next worksheets I am wanting t
list the tasks for each individual person from the first worksheet.
can do this, however the only way I have found to do this is eithe
simply using =Sheet1!B10 or with paste link. This doesnt however allo
the individual to update the main list from his area. I haven
explained that very well but hopefully you get the gist of it. Any hel
would be gratefully appriciated.

Thanks

Mic
 
J

JLatham

There's one way to do this easily, but all sheets have to be duplicates of
each other - with duplicate layouts. You'd have to mark which tasks each
individual is responsible for, but still have the others listed in the same
order on each sheet. You could color code the ones for each individual or
hide the ones not to be assigned. We could even work up some automation to
do the hiding thing - but that's beyond the immediate problem.

Set up a second column on the first sheet to record a "Completed" indicator.
Now, assuming that the first worksheet for the individual IT persons is
named ADAM and the last one is named ZIFF, and your task list is in column A
and starts in row 2, put a formula like this one in cell B2:
=IF(COUNTA(ADAM:ZIFF!B2)>0,"Completed","")
Drag that down the column to the end of the tasks list.

Then as the IT folks complete their tasks, they put some indicator in column
B on their individual sheets next to the task title and you'll know it was
completed by someone in the stack.

How it works: picture the ADAM:ZIFF!B2 portion as kind of a hole punch
through the entire stack of worksheets starting with ADAM and ending with
ZIFF and including any in between them in the workbook, and checking the
specified cell on all of them at once. The 3-dimensional equivalent of a
similar function used on a single sheet for a row or column of information.

I used COUNTA() so that they can enter anything in their sheets in column B
and it will be detected. It could be an "x" or the word "done" or the date
completed, or just about any non-blank entry.
 
Top