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

    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
    Posts
    1,091
    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.
    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
    Mar 2006
    Posts
    163
    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
    Posts
    10
    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
  •