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.

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.

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.