if statement, change cell only if true


L

laavista

I know there has to be a simple solution, but I can't find it

Sheet1 has values in column A. Col A is formatted as text and contains
either an
ID 'number' or the words NOTES

Sheet2 cells are linked to the cells in Sheet1.

If an ID in Sheet1 is changed, then Sheet2 cell needs to change, but if
Sheet1 cell changes to the word NOTES, then Sheet2 needs to retain the
original ID.

For example
Sheet1, Col A Sheet2, Col A
Row 2 1234 1234
Row 3 5678 5678
Row 4 9999 9999

The value in Sheet1, row2 is changed to 3838, then Sheet2, row2 should be
3838.
The value in Sheet1, row3 is changed to NOTES; Sheet2, Row3 should remain 5678

I tried putting a formula in Sheet2 cell:
=IF(Sheet1!A2<>"NOTES",Sheet1A2) (which gives me a circular reference)

Your help would really be appreciated!
 
Ad

Advertisements

J

Jacob Skaria

Select the sheet1 tab. Right click the sheet tab and click on 'View Code'.
This will launch VBE. Paste the below code to the right blank portion. Get
back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
If IsNumeric(Target.Value) Then
Sheets("Sheet2").Range(Target.Address) = Target.Value
End If
End If
End Sub
 
S

Steve Dunn

Circular references aren't always a bad thing, but can occasionally be
troublesome, which is why it is disabled by default.
To allow circular referencing, turn on "Enable iterative calculation"* from
options, then in Sheet2!A2 use:

=IF(Sheet1!A2<>"NOTES",Sheet1A2,A2)

HTH
Steve D.


* <rant> Unfortunately, like the other calculation options, this is an
application wide setting rather than a per workbook setting, and will be
affected by the first workbook you open in any Excel session. i.e. if the
first workbook you open in any session happens to have that setting turned
off, then you will need to check it if you open a workbook (in the same
session) that requires it. I don't know Microsoft's justification for the
scope of the calculation settings, but personally I find it a constant
annoyance. <\rant>
 
L

laavista

Thank you!

Jacob Skaria said:
Select the sheet1 tab. Right click the sheet tab and click on 'View Code'.
This will launch VBE. Paste the below code to the right blank portion. Get
back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then
If IsNumeric(Target.Value) Then
Sheets("Sheet2").Range(Target.Address) = Target.Value
End If
End If
End Sub
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top