In col A I have a list of names with several blank cells between each name. The number of blank cells between each name can vary. In total I may have 500 names listed, covering a range of 3000+ cells.

I need to fill in the blank cells between names with the name preceding each small block of blanks, making a complete column of cells containing blocks of names. Thought it would be easy. I have tried to use a function/foumula in an adjacent blank column i.e. in col B but just get bogged down with TRUE FALSE responses and no real data. i.e.

Col A Col B
Name 1
Blank 1
Blank 2
Blank 3
Name 2
Blank 4

I would be grateful to know what function/foumula I can use consistenly in col B to fill in the blanks in Col 1 with the name at the top of each block of blank cells. I want Blanks 1,2 and 3 to equal Name1 and Blank 4 to equal Name 2 etc. I do not want to fill down each block of blanks manually. Any assistance woul be very much appreciated.

Howdy. One way to do this:

Define a named formula. Select a cell (not in row 1, say B3) (Go to Insert > Name > Define)

Use CellAbove as the name and as the reference type in:

=!B2

(no dollars signs)

Click OK.

Then Select your data (A1:A3000), and go to

Edit > Go To > Special and choose Blanks. Click OK. Then in the formula bar, type in

=CellAbove and hit CTRL + ENTER (not just ENTER)

If you don't want to keep the formulas, then copy, paste special values.

1. Select the column 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.

Shades and Norie thanks guys for your replies. These do just what I needed. I am very grateful for your help. Best regards.