Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 value change
I am looking for a function or formula that will allow me to do an IF Then
statement. For example, I want to say if cell B1 contains CM or RT then make cell D1 negative value. Any suggestions are greatly appreciated. -- narp |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 value change
Use the =IF function. Something like:
A B 1 CM 2 $100 3 =IF(OR(A1="CM",A1="RT"),-A2,A2) hth "narp" wrote: I am looking for a function or formula that will allow me to do an IF Then statement. For example, I want to say if cell B1 contains CM or RT then make cell D1 negative value. Any suggestions are greatly appreciated. -- narp |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 value change
This is awasome!!! We have been trying to make this an automatic process for
a long time. This will save me and my co-worker a lot of time every month. Do you have any tricks for changing a cell that reads CM-IN1234 to be IN1234-CM? This is the other process I'm trying to automate. -- narp "pdberger" wrote: Use the =IF function. Something like: A B 1 CM 2 $100 3 =IF(OR(A1="CM",A1="RT"),-A2,A2) hth "narp" wrote: I am looking for a function or formula that will allow me to do an IF Then statement. For example, I want to say if cell B1 contains CM or RT then make cell D1 negative value. Any suggestions are greatly appreciated. -- narp |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 value change
Narp --
Do you mean actually going through a list of cells and changing the text that's in them, or some kind of conditional formula where, depending on the value of a cell, you either subtract numbers or add them? "narp" wrote: This is awasome!!! We have been trying to make this an automatic process for a long time. This will save me and my co-worker a lot of time every month. Do you have any tricks for changing a cell that reads CM-IN1234 to be IN1234-CM? This is the other process I'm trying to automate. -- narp "pdberger" wrote: Use the =IF function. Something like: A B 1 CM 2 $100 3 =IF(OR(A1="CM",A1="RT"),-A2,A2) hth "narp" wrote: I am looking for a function or formula that will allow me to do an IF Then statement. For example, I want to say if cell B1 contains CM or RT then make cell D1 negative value. Any suggestions are greatly appreciated. -- narp |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 value change
I am trying to accomplish several things in this one worksheet:
1. Change CM-IN1234 to IN1234-CM 2. Change data value in C2 to a negative # if CM or RT is in cell B2 3. Change color of cells with negative values Here is what my worksheet looks like. DOCDATE SOPNUMBE QUANTITY UNITCOST 01/23/08 CM-IN22664 1 $2.12 01/24/08 CM-IN22664A 1 $2.12 01/23/08 CM-IN22665 1 $77.31 01/23/08 CM-IN22665 1300 $2.31 01/23/08 CM-IN22665 600 $2.12 01/23/08 CM-IN22665 800 $16.30 01/23/08 CM-IN22667 2 $96.00 01/03/08 IN22036 1 $101.93 01/03/08 IN22037 1 $33.62 01/03/08 IN22038 1 $- 01/03/08 IN22038 1 $14.00 Also, do you have any suggestions on reference material that would help me understand and create functions? -- narp "pdberger" wrote: Narp -- Do you mean actually going through a list of cells and changing the text that's in them, or some kind of conditional formula where, depending on the value of a cell, you either subtract numbers or add them? "narp" wrote: This is awasome!!! We have been trying to make this an automatic process for a long time. This will save me and my co-worker a lot of time every month. Do you have any tricks for changing a cell that reads CM-IN1234 to be IN1234-CM? This is the other process I'm trying to automate. -- narp "pdberger" wrote: Use the =IF function. Something like: A B 1 CM 2 $100 3 =IF(OR(A1="CM",A1="RT"),-A2,A2) hth "narp" wrote: I am looking for a function or formula that will allow me to do an IF Then statement. For example, I want to say if cell B1 contains CM or RT then make cell D1 negative value. Any suggestions are greatly appreciated. -- narp |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 value change
Narp --
Three issues: 1) Use 'Find and Replace'. Select the cells you want to affect. Either press EditReplace or press CTRL-H. Enter the text you want to change (CM-IN1234) and then what you want to change it to (IN1234-CM). Replace all. 2) Something like: =IF(ISNUMBER(FIND("CM",B2)),D2,-D2) 3) Use conditional formatting. Select the cells you want to affect. Press FormatConditional Formatting. Using the formula for the topmost cell, select the options so that the cell value is < 0, then click the buttons to set the text and cell formats you want to see. HTH "narp" wrote: I am trying to accomplish several things in this one worksheet: 1. Change CM-IN1234 to IN1234-CM 2. Change data value in C2 to a negative # if CM or RT is in cell B2 3. Change color of cells with negative values Here is what my worksheet looks like. DOCDATE SOPNUMBE QUANTITY UNITCOST 01/23/08 CM-IN22664 1 $2.12 01/24/08 CM-IN22664A 1 $2.12 01/23/08 CM-IN22665 1 $77.31 01/23/08 CM-IN22665 1300 $2.31 01/23/08 CM-IN22665 600 $2.12 01/23/08 CM-IN22665 800 $16.30 01/23/08 CM-IN22667 2 $96.00 01/03/08 IN22036 1 $101.93 01/03/08 IN22037 1 $33.62 01/03/08 IN22038 1 $- 01/03/08 IN22038 1 $14.00 Also, do you have any suggestions on reference material that would help me understand and create functions? -- narp "pdberger" wrote: Narp -- Do you mean actually going through a list of cells and changing the text that's in them, or some kind of conditional formula where, depending on the value of a cell, you either subtract numbers or add them? "narp" wrote: This is awasome!!! We have been trying to make this an automatic process for a long time. This will save me and my co-worker a lot of time every month. Do you have any tricks for changing a cell that reads CM-IN1234 to be IN1234-CM? This is the other process I'm trying to automate. -- narp "pdberger" wrote: Use the =IF function. Something like: A B 1 CM 2 $100 3 =IF(OR(A1="CM",A1="RT"),-A2,A2) hth "narp" wrote: I am looking for a function or formula that will allow me to do an IF Then statement. For example, I want to say if cell B1 contains CM or RT then make cell D1 negative value. Any suggestions are greatly appreciated. -- narp |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 value change
On issue 1 is there a way to make these changes in a column when each cell
has a different number after the CM-IN? Thank you so much for all the help....you are a life saver. -- narp (Noelle) "pdberger" wrote: Narp -- Three issues: 1) Use 'Find and Replace'. Select the cells you want to affect. Either press EditReplace or press CTRL-H. Enter the text you want to change (CM-IN1234) and then what you want to change it to (IN1234-CM). Replace all. 2) Something like: =IF(ISNUMBER(FIND("CM",B2)),D2,-D2) 3) Use conditional formatting. Select the cells you want to affect. Press FormatConditional Formatting. Using the formula for the topmost cell, select the options so that the cell value is < 0, then click the buttons to set the text and cell formats you want to see. HTH "narp" wrote: I am trying to accomplish several things in this one worksheet: 1. Change CM-IN1234 to IN1234-CM 2. Change data value in C2 to a negative # if CM or RT is in cell B2 3. Change color of cells with negative values Here is what my worksheet looks like. DOCDATE SOPNUMBE QUANTITY UNITCOST 01/23/08 CM-IN22664 1 $2.12 01/24/08 CM-IN22664A 1 $2.12 01/23/08 CM-IN22665 1 $77.31 01/23/08 CM-IN22665 1300 $2.31 01/23/08 CM-IN22665 600 $2.12 01/23/08 CM-IN22665 800 $16.30 01/23/08 CM-IN22667 2 $96.00 01/03/08 IN22036 1 $101.93 01/03/08 IN22037 1 $33.62 01/03/08 IN22038 1 $- 01/03/08 IN22038 1 $14.00 Also, do you have any suggestions on reference material that would help me understand and create functions? -- narp "pdberger" wrote: Narp -- Do you mean actually going through a list of cells and changing the text that's in them, or some kind of conditional formula where, depending on the value of a cell, you either subtract numbers or add them? "narp" wrote: This is awasome!!! We have been trying to make this an automatic process for a long time. This will save me and my co-worker a lot of time every month. Do you have any tricks for changing a cell that reads CM-IN1234 to be IN1234-CM? This is the other process I'm trying to automate. -- narp "pdberger" wrote: Use the =IF function. Something like: A B 1 CM 2 $100 3 =IF(OR(A1="CM",A1="RT"),-A2,A2) hth "narp" wrote: I am looking for a function or formula that will allow me to do an IF Then statement. For example, I want to say if cell B1 contains CM or RT then make cell D1 negative value. Any suggestions are greatly appreciated. -- narp |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 value change
I don't know how to do that other than to write a macro. Unless you have
lots and lots of cells to change, it would be easier to do them manually, I think. Maybe someone else in discussions knows how. On the other stuff at least, glad I could help. "narp" wrote: On issue 1 is there a way to make these changes in a column when each cell has a different number after the CM-IN? Thank you so much for all the help....you are a life saver. -- narp (Noelle) "pdberger" wrote: Narp -- Three issues: 1) Use 'Find and Replace'. Select the cells you want to affect. Either press EditReplace or press CTRL-H. Enter the text you want to change (CM-IN1234) and then what you want to change it to (IN1234-CM). Replace all. 2) Something like: =IF(ISNUMBER(FIND("CM",B2)),D2,-D2) 3) Use conditional formatting. Select the cells you want to affect. Press FormatConditional Formatting. Using the formula for the topmost cell, select the options so that the cell value is < 0, then click the buttons to set the text and cell formats you want to see. HTH "narp" wrote: I am trying to accomplish several things in this one worksheet: 1. Change CM-IN1234 to IN1234-CM 2. Change data value in C2 to a negative # if CM or RT is in cell B2 3. Change color of cells with negative values Here is what my worksheet looks like. DOCDATE SOPNUMBE QUANTITY UNITCOST 01/23/08 CM-IN22664 1 $2.12 01/24/08 CM-IN22664A 1 $2.12 01/23/08 CM-IN22665 1 $77.31 01/23/08 CM-IN22665 1300 $2.31 01/23/08 CM-IN22665 600 $2.12 01/23/08 CM-IN22665 800 $16.30 01/23/08 CM-IN22667 2 $96.00 01/03/08 IN22036 1 $101.93 01/03/08 IN22037 1 $33.62 01/03/08 IN22038 1 $- 01/03/08 IN22038 1 $14.00 Also, do you have any suggestions on reference material that would help me understand and create functions? -- narp "pdberger" wrote: Narp -- Do you mean actually going through a list of cells and changing the text that's in them, or some kind of conditional formula where, depending on the value of a cell, you either subtract numbers or add them? "narp" wrote: This is awasome!!! We have been trying to make this an automatic process for a long time. This will save me and my co-worker a lot of time every month. Do you have any tricks for changing a cell that reads CM-IN1234 to be IN1234-CM? This is the other process I'm trying to automate. -- narp "pdberger" wrote: Use the =IF function. Something like: A B 1 CM 2 $100 3 =IF(OR(A1="CM",A1="RT"),-A2,A2) hth "narp" wrote: I am looking for a function or formula that will allow me to do an IF Then statement. For example, I want to say if cell B1 contains CM or RT then make cell D1 negative value. Any suggestions are greatly appreciated. -- narp |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I change the menubar to look like excel 2003? | New Users to Excel | |||
How to change every second row with different color - Excel 2003 | New Users to Excel | |||
Change the way numbers are displayed in Excel 2003 SP2 | Excel Discussion (Misc queries) | |||
How can I change the formula bar in excel 2003? | Excel Discussion (Misc queries) | |||
How do I change the Tab spacing in Excel 2003? | Excel Worksheet Functions |