I am trying to accomplish the following: If the value on Sheet 1 Cell K3 is
blank, then the value on Sheet 2 Cell K3 is automatically left blank.
Otherwise, any value entered on Sheet 1 Cell K3 will automatically be copied
to Sheet 2 Cell K3. It is possible that the value entered in Sheet 1 Cell K3
is preceeded by one or more zeros, so I have formatted Sheet 2 Cell K3 as
'text'. I have tried the formula =IF(Sheet1!K3=0,"",(Sheet1!K3)) in Sheet2
Cell K3, which doesn't work. Thanks for your help!

Regards,
Larry

Formulas return Values, not formats. To copy the format, you'll need
VBA. One way:

put this in your Sheet 1 code module (right-click the Sheet 1 tab and
choose View Code):


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "K3" Then
With Sheets("Sheet2").Range("K3")
.NumberFormat = "@"
.Value = Target.Text
End With
End If
End Sub




In article ,
"Larry" wrote:

I am trying to accomplish the following: If the value on Sheet 1 Cell K3 is
blank, then the value on Sheet 2 Cell K3 is automatically left blank.
Otherwise, any value entered on Sheet 1 Cell K3 will automatically be copied
to Sheet 2 Cell K3. It is possible that the value entered in Sheet 1 Cell K3
is preceeded by one or more zeros, so I have formatted Sheet 2 Cell K3 as
'text'. I have tried the formula =IF(Sheet1!K3=0,"",(Sheet1!K3)) in Sheet2
Cell K3, which doesn't work. Thanks for your help!

Thanks. That formula didn't accomplish what I hoped. I'm not concerned
about the formatting, just the IF formula to copy anything placed in Sheet 1
Cell K3 to Sheet 2 Cell K3. If Sheet 1 Cell K3 is blank, the Sheet 2 Cell K3
should be blank. Otherwise, any valu placed in Sheet 1 Cell K3 should be
copied to Sheet 2 Cell K3. I can work around the zero value defaults.

"JE McGimpsey" wrote:

Formulas return Values, not formats. To copy the format, you'll need
VBA. One way:

put this in your Sheet 1 code module (right-click the Sheet 1 tab and
choose View Code):


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "K3" Then
With Sheets("Sheet2").Range("K3")
.NumberFormat = "@"
.Value = Target.Text
End With
End If
End Sub




In article ,
"Larry" wrote:

I am trying to accomplish the following: If the value on Sheet 1 Cell K3 is
blank, then the value on Sheet 2 Cell K3 is automatically left blank.
Otherwise, any value entered on Sheet 1 Cell K3 will automatically be copied
to Sheet 2 Cell K3. It is possible that the value entered in Sheet 1 Cell K3
is preceeded by one or more zeros, so I have formatted Sheet 2 Cell K3 as
'text'. I have tried the formula =IF(Sheet1!K3=0,"",(Sheet1!K3)) in Sheet2
Cell K3, which doesn't work. Thanks for your help!

If you're not concerned with formatting, just put this in Sheet2!K3:

=IF(Sheet1!K3="","",Sheet1!K3)

In article ,
"Larry" wrote:

Thanks. That formula didn't accomplish what I hoped. I'm not concerned
about the formatting, just the IF formula to copy anything placed in Sheet 1
Cell K3 to Sheet 2 Cell K3. If Sheet 1 Cell K3 is blank, the Sheet 2 Cell K3
should be blank. Otherwise, any valu placed in Sheet 1 Cell K3 should be
copied to Sheet 2 Cell K3. I can work around the zero value defaults.