excel 2003 value change

  • Thread starter Thread starter narp
  • Start date Start date
N

narp

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.
 
Use the =IF function. Something like:

A B
1 CM
2 $100
3 =IF(OR(A1="CM",A1="RT"),-A2,A2)

hth
 
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 --

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?
 
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 --

Three issues:

1) Use 'Find and Replace'. Select the cells you want to affect. Either
press >Edit>Replace 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
Format>Conditional Formatting. Using the formula for the topmost cell,
Click to expand...
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
 
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.
 
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.
 
Back
Top