Equal formula keeping both content and format

[Laetitia]

Hi, I would like to enter "equal formulas" that would keep the value and
format of the cells of origins from another worksheet, so that when I change
a value or format in a cell from worksheet 1, it is automatically corrected
in the worksheet 2. Is it possible? how can I do this? Thanks a lot in
advance!

 

[Mike H]

Hi,

Put this in sheet 2 (or any sheet) and whever A1 in sheet 1 is changed the
cell with this in also changes

=Sheet1!A1

Mike

 

[CLR]

Formulas, such as =Sheet1!A1 only convey cell Values, not formatting. You
could also transfer formatting if the Conditional Formatting feature were
employed on both sheets..........

Vaya con Dios,
Chuck, CABGx3

 

[David Biddulph]

In what way does that respond to a format change on sheet 1, Mike?

 

[Laetitia]

Thanks Mike, but the problem with this is that it keeps only the value but
not the format and I absolutely need the format to be the same.
For example if I have in sheet 1 cell A1= 12 (in red and bold), I then need
in sheet 2 cell B3 to be equal to A1 that 12 but also in red and bold.

How can I do this?

Thanks
Laetitia

 

[Carim]

Hi Laetitia,

If you want to replicate your worksheets (format, contents, ...) each
sheet should carry its own event macro :

Private Sub Worksheet_Deactivate()
Sheet1.Cells.Copy Destination:=Sheet2.Range("A1")
End Sub

HTH

 

[Laetitia]

Hi Carim, thanks for your answer. Except that I have to admit that I'm not
an expert when it comes to "event macro". How can I apply what you've
indicated concretely?

thanks a lot!
Laetitia

 

[Laetitia]

Anybody to help on the below?
thanks!

Hi Carim, thanks for your answer. Except that I have to admit that I'm not
an expert when it comes to "event macro". How can I apply what you've
indicated concretely?

thanks a lot!
Laetitia

 

[Gord Dibben]

Carim's event code just copies every cell on sheet1 to sheet2.

I don't think you want that.

Hopefully Carim will post back with an explanation.


Gord Dibben MS Excel MVP

 

[Carim]

@ Laetitia,

Go to the worksheet tab,
right click to menu sub menu,
select View Code
and copy event macro :

Private Sub Worksheet_Deactivate()
Sheet1.Cells.Copy Destination:=Sheet2.Range("A1")
End Sub

@ Gord ,
You are obviously right ...
But in my humble opinion, to fully replicate a sheet with values and
formats, there is no real alternative ...
Don't you think so ?