i have a column containing numbers, every other row there is an empty row, i
would like to copy the cell from above with the opposite sign to that empty
row, how can i do that with a formula or macro. So that i don't have to do
that one by one. Thanks a lot.

One way:

Assume your range is A2:A10. A3, 5, 7, 9 are empty

Select the range
Hit function key F5
Click the Special button
Select Blanks
OK

All the empty cell *within* the range are now selected and cell A3 is the
active cell.

Enter this formula in the formula bar:

=OFFSET(A3,-1,,)*-1

Hit CTRL ENTER

That will fill the empty cells *within* the range leaving the last entry to
be done manually (should be no big deal!)

Biff

"andresg1975" <(E-Mail Removed)> wrote in message
news:898C63B7-FB57-4D9D-85D0-(E-Mail Removed)...
>i have a column containing numbers, every other row there is an empty row,
>i
> would like to copy the cell from above with the opposite sign to that
> empty
> row, how can i do that with a formula or macro. So that i don't have to do
> that one by one. Thanks a lot.

Select the column.

F5>Special>Blanks>OK

Type an = sign in the active cell then point(left-click)in cell above and type
*-1 then hit CTRL + ENTER

You can copy those formula cells later as values.


Gord Dibben MS Excel MVP


On Wed, 20 Sep 2006 14:09:01 -0700, andresg1975
<(E-Mail Removed)> wrote:

>i have a column containing numbers, every other row there is an empty row, i
>would like to copy the cell from above with the opposite sign to that empty
>row, how can i do that with a formula or macro. So that i don't have to do
>that one by one. Thanks a lot.

Another very similar way:

Select the range to fix
edit|goto|special|check blanks
type the equal sign
then hit the up arrow
then type *-1
and hit ctrl-enter to fill the blank cells with your formula.


andresg1975 wrote:
>
> i have a column containing numbers, every other row there is an empty row, i
> would like to copy the cell from above with the opposite sign to that empty
> row, how can i do that with a formula or macro. So that i don't have to do
> that one by one. Thanks a lot.

--

Dave Peterson