Copy/move cell format & comment in cell reference

I have a lot of cell in a spreadsheet that references cell on another spreadsheet (on the same drive). The problem I have is that the cell reference copies the data only, not the cell format (e.g. font type, shading ...etc.) and also not the comment if one is present. Is there a way also copy not just the data but also the cell format, and also comments?

What I'm trying to do:
Say I have 2 files: john.xls and jane.xls (for John and Jane to record their vacation).
Row 2 on both spreadsheet lists John's vacation schedule, and row 3 is Jane's. John enters his vacation schedule in John.xls, therefore row 2 is not protected, but row 3 is protected and each cell contains a formula that references the same address on Jane.xls. The idea is for John to see Jane's schedule on his own spreadsheet, and is updated whenever Jane updates her schedule. There are times when Jane may want to either add a comment to a particular cell or format the cell with color/font, and I would like to reflect these formatting in John's file.

I have done a lot of research but found nothing so far, so I'm now wondering if this is possible. If there's another way of achieving the same results (i.e. instead of cell referencing), I would also like to hear the idea.

Thanks.

Hi,
it is not possible as far as I know. Only one chance is to write a code for format/comment copy. This macro could be started by any change event of the target (not original!) file or by a button. I would reccomand the second possibility in your case. It means to update information about Janes vacation in Johns file whent John open his workbook and later when he click the button "update Janes vacation".
Regards
Petr

Originally Posted by PetrH

Hi,
it is not possible as far as I know. Only one chance is to write a code for format/comment copy. This macro could be started by any change event of the target (not original!) file or by a button. I would reccomand the second possibility in your case. It means to update information about Janes vacation in Johns file whent John open his workbook and later when he click the button "update Janes vacation".
Regards
Petr

I'm actually not very good at all with Excel ..... can you explain how each "possibility" is done?

The code is the same in both cases and consists of
1) a cycle going through all cells with formula in Johns list
2) an if condition testing if the formula refers to Janes file. If so then
3) copy of refered cell format and its application to refering cell.
The difference is just in way how the macro is launched.
I'll try to write some example code, but not today.
Have a nice day
Petr