get same format from referenced cell

[bobby]

How do you get the format of a referenced cell to
transfer with the data?

 

[Frank Kabel]

Hi
this is not possible for Excel functions. Formulas can only return
values not formats

 

[JE McGimpsey]

using VBA. Put this in your worksheet code module (right-click on the
worksheet tab and choose view code):

Assume the formula is in Sheet1 cell A1 and the referenced cell is in,
Sheet2 cell J10:


Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Sheets("Sheet2").Range("J10").Copy
Range("A1").PasteSpecial Paste:=xlFormats
Application.EnableEvents = True
End Sub

Note that this won't immediately update if the format in Sheet2!J10
changes - there's no event fired by a format change. You'll need to
press F9 or otherwise cause a calculation.

 

[Ozzie]

It should be automatic. Try paste special - all.

 

[A.W.J. Ales]

Ozzie,

It is automatic when you copy, but not when there is just a link between
dependent cell A1 ( =J10) and source (J10)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *