Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004

    Unanswered: Fill Down into blocks of blank cells

    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.

  2. #2
    Join Date
    Oct 2003
    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:


    (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.
    old, slow, and confused
    but at least I'm inconsistent!

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

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2006
    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.

  4. #4
    Join Date
    Oct 2004
    Shades and Norie thanks guys for your replies. These do just what I needed. I am very grateful for your help. Best regards.

Posting Permissions

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