Sheets Linking

  • Thread starter Thread starter neelam
  • Start date Start date
N

neelam

Hi all,

I have Sheet1 having collective data. Having information for individual
Directors scattered on the spreadsheet mainly in Test format..... i.e
response to particular action or progress report....

Using code I filtered and copied data along with format to individual
Directors named sheet. So that when they click sheet of their name they
can see only their respective data........

Now I want :

When they edit their data on their respective sheet shld update on the
Main sheet at the same original location.

PLS TAKE CARE DATA IS SCATTERED ON THE SHEET ALL OVER...
Its not grouped & shld not be grouped on Sheet1(main) sheet.

Looking fwd to hear earliest ....

Trying hard since 2 days........
need urgently for my job.......

Thanks
neelam
 
Here's one way to do it:

Have one sheet in the workbook, possibly a hidden sheet, depending on your
preference, called "temp". Make a ThisWorkbook procedure that is activated
when someone clicks on one of the sheets for the Directors, call it
"SheetIn" and another event procedure when the workbook is closed or another
sheet is clicked, call it "SheetOut". The "SheetIn" procedure copies the
contents of the clicked sheet to the "temp" sheet (I say "copy" but you will
want to do this using a statement like Range2.Value = Range1.Value). When
the selected sheet changes or the workbook is closed, the contents of
"temp" are compared to the contents of the sheet that was opened. If any of
the cells have changed, search the main sheet for the contents of the cell
on temp which corresponds the the cell on the Director sheet that was
changed and replace the found cell on the main sheet with the new contents.
Clear the "temp" sheet when finished.

This could also be done with a 2D array, having cell address in one side of
the array and cell contents in the other.

HTH,
Shockley
 
Back
Top