| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statement, change cell only if true
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statement, change cell only if true
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 -- Jacob (MVP - Excel) "laavista" wrote: 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statement, change cell only if true
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 "laavista" wrote in message ... 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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statement, change cell only if true
Thank you!
"Jacob Skaria" wrote: 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 -- Jacob (MVP - Excel) "laavista" wrote: 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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statement, change cell only if true
Thank you so much for taking the time to respond. This really helped!
"Steve Dunn" wrote: 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 "laavista" wrote in message ... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| TRUE/FALSE STATEMENT | Excel Discussion (Misc queries) | |||
| Two TRUE to one FALSE statement | Excel Worksheet Functions | |||
| Return blank cell if 'find' statement not true | Excel Worksheet Functions | |||
| Use IF to change value in a different cell when TRUE (or FALSE)? | Excel Discussion (Misc queries) | |||
| Want to change the color of a true/false logical statement with i. | Excel Worksheet Functions | |||
Linear Mode
