Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy cell from above with opposite sign
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy cell from above with opposite sign
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy cell from above with opposite sign
Select the column.
F5SpecialBlanksOK 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 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy cell from above with opposite sign
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
how to count the number of text frequencies and copy to other cell | Excel Worksheet Functions | |||
hpw do I logic test a cell then copy the row to diff. SS | Excel Worksheet Functions | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) |