Multisheet IF Function

[Larry]

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

[JE McGimpsey]

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 <02139BE0-F5FF-44D2-B2F8-98CECB8C0CDF@microsoft.com>,
"Larry" <Larry@discussions.microsoft.com> 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!

[tkstock]

Any leading zeros will be automatically dropped in Sheet1, ergo they won't appear in sheet two. If you wan't your leading zeros to appear, Sheet1 will have to be formatted to text. Then, the following formula will work:

Please Login or Register  to view this content.

[Larry]

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 <02139BE0-F5FF-44D2-B2F8-98CECB8C0CDF@microsoft.com>,
> "Larry" <Larry@discussions.microsoft.com> 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!
>

[JE McGimpsey]

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

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

In article <9998D99D-B508-4BBB-97EE-6E20F9DEAA80@microsoft.com>,
"Larry" <Larry@discussions.microsoft.com> 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.

[tkstock]

Thanks. That formula didn't accomplish what I hoped.

Who are you replying to? Are you using the formula in Sheet2? Your original formula should work fine except you need to check for empty string "" instead of 0.

I can work around the zero value defaults

What zero-value defaults? Does your Sheet1!K3 cell always have a value in it? Does it have a formula?