I have a worksheet with a column indicating "Receipts," "Deliveries," "In,"
and "Out." A second column has a series of values, all positive numbers. I
need to change all the "Deliveries" and "Out" to negative values. I have
tried filtering on "Deliveries" and "Out" and then using the Paste Special
function, but when I remove the filter I find that every value has been
changed to negative, not just the filtered records.

Any suggestions on how to change just the desired values from positive to
negative?

Insert new column next to number (helper column)
I will assume text in A, numbers in B
In C2 enter =IF(OR(A2="A",A2="B"),-1,1)*B2
replace A and B by your text
Copy down column
Select all the new formulas in C and Copy; use Paste Special Formulas
Delete old B column
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BJ" wrote in message
...
I have a worksheet with a column indicating "Receipts," "Deliveries,"
"In,"
and "Out." A second column has a series of values, all positive numbers.
I
need to change all the "Deliveries" and "Out" to negative values. I have
tried filtering on "Deliveries" and "Out" and then using the Paste Special
function, but when I remove the filter I find that every value has been
changed to negative, not just the filtered records.

Any suggestions on how to change just the desired values from positive to
negative?

Thank you very much! That was a huge help!

"Bernard Liengme" wrote:

Insert new column next to number (helper column)
I will assume text in A, numbers in B
In C2 enter =IF(OR(A2="A",A2="B"),-1,1)*B2
replace A and B by your text
Copy down column
Select all the new formulas in C and Copy; use Paste Special Formulas
Delete old B column
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BJ" wrote in message
...
I have a worksheet with a column indicating "Receipts," "Deliveries,"
"In,"
and "Out." A second column has a series of values, all positive numbers.
I
need to change all the "Deliveries" and "Out" to negative values. I have
tried filtering on "Deliveries" and "Out" and then using the Paste Special
function, but when I remove the filter I find that every value has been
changed to negative, not just the filtered records.

Any suggestions on how to change just the desired values from positive to
negative?

Autofilter the data as you want then copy a cell with -1 in it, then select
the filtered data and select Edit GoTo Special and select Visible cells
only then do your Paste Special Multiply

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"BJ" wrote in message
...
I have a worksheet with a column indicating "Receipts," "Deliveries,"
"In,"
and "Out." A second column has a series of values, all positive numbers.
I
need to change all the "Deliveries" and "Out" to negative values. I have
tried filtering on "Deliveries" and "Out" and then using the Paste Special
function, but when I remove the filter I find that every value has been
changed to negative, not just the filtered records.

Any suggestions on how to change just the desired values from positive to
negative?