Multisheet IF Function
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 |
Multisheet IF Function
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! |
Multisheet IF Function
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! |
Multisheet IF Function
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. |
All times are GMT +1. The time now is 12:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com