Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Unanswered: Expressions - to set below rows equal to the row above.

    I am trying implement a expression or formula that applies to the entire work sheet or selected columns.

    I have an excel worksheet that has a lot of spaces or empty rows in the columns between entries. I need to almost set a "if statement in excel", like if there is an entry, all the empty spaces or rows below the entry will be the same or equal to it, essentally copy.

    I am trying to eliminate manual copying and pasting.

    thanks in advance.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, one way to do this, is to name a formula. Let's say you click in cell E6

    Then go to INSERT > NAME > DEFINE. In the name section type "CallAbove". Then in the range box below put this:

    =Sheet1!E5

    (Assuming the active sheet is Sheet1). Note that there are no Dollar signs before either one.

    Now, click anywhere in your table of values. Then hit CTRL + G to bring up Replace dialog. In the lower left corner is "Special". Click that button, and in the resulting dialog, click on "Blanks". Click OK. Then with all blanks selected, go to the formula bar, and type in:

    =CellAbove

    and hit CTRL + ENTER

    Everything will be filled with the data above.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by shades
    Howdy, one way to do this, is to name a formula. Let's say you click in cell E6

    Then go to INSERT > NAME > DEFINE. In the name section type "CallAbove". Then in the range box below put this:

    =Sheet1!E5

    (Assuming the active sheet is Sheet1). Note that there are no Dollar signs before either one.

    Now, click anywhere in your table of values. Then hit CTRL + G to bring up Replace dialog. In the lower left corner is "Special". Click that button, and in the resulting dialog, click on "Blanks". Click OK. Then with all blanks selected, go to the formula bar, and type in:

    =CellAbove

    and hit CTRL + ENTER

    Everything will be filled with the data above.
    Hi, Thanks for the reply.

    I did everything you said. I got "#NAME?" in all my blanks.
    There is only 1 sheet in my excel file. Named = 2007.

    Visual:
    Row: Column 1 Column 2
    2. James Icecream
    3.
    4.
    5.
    6.
    7. Mike Candies
    8.
    9.
    10.
    11.
    This is just a small section of the data. For I want Column 1/Row 2-6 = James, Column 2/Row2-6 = Icecream. And column1/row 7-11 = mike, column2/row 7-11 = candies.

    How to solve this problem?
    Hope this helps a bit.
    Thanks in advance.

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    1. Select the column(s) with the blanks.
    2. Goto Edit>Goto...Special and select blanks.
    3. Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
    4. Select the column, copy and paste special values.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •