I'm trying to work with an export from a Lotus Notes db <grumble>. My first
column contains sporadic entries (with blank cells between), as each of the
column A entries are associated with column B entries in the rows where the
column A cell is empty. For example:

red
apple
firetruck
wagon
yellow
canary
lemon

If cell A1 is "red" in my example, how can I autopopulate A2-A4 to also be
"red" and A6-A7 to autopopulate with "yellow" based on that value in A5? Is
there some formular expression that I could use. I'm not VisualBasic
skilled, but could something simple be done there?

Select column A and F5>Special>Blanks>OK

Enter an = sign in active cell then point or arrow up to cell above and hit CTRL
+ ENTER


Gord Dibben MS Excel MVP

On Fri, 10 Aug 2007 09:50:01 -0700, Jay S. <Jay S.@discussions.microsoft.com>
wrote:

>I'm trying to work with an export from a Lotus Notes db <grumble>. My first
>column contains sporadic entries (with blank cells between), as each of the
>column A entries are associated with column B entries in the rows where the
>column A cell is empty. For example:
>
>red
> apple
> firetruck
> wagon
>yellow
> canary
> lemon
>
>If cell A1 is "red" in my example, how can I autopopulate A2-A4 to also be
>"red" and A6-A7 to autopopulate with "yellow" based on that value in A5? Is
>there some formular expression that I could use. I'm not VisualBasic
>skilled, but could something simple be done there?

To populate it down, you could try the methods described in
either of these 2 excellent pages by:

1. Debra Dalgleish:
http://www.contextures.com/xlDataEntry02.html
Excel -- Data Entry -- Fill Blank Cells

Fill Blank Cells
Fill Blank Cells Programmatically**
**Sub FillColBlanks() by Dave Peterson

2. David McRitchie:
http://www.mvps.org/dmcritchie/excel/fillempt.htm
Fill in the Empty Cells
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jay S." wrote:
> I'm trying to work with an export from a Lotus Notes db <grumble>. My first
> column contains sporadic entries (with blank cells between), as each of the
> column A entries are associated with column B entries in the rows where the
> column A cell is empty. For example:
>
> red
> apple
> firetruck
> wagon
> yellow
> canary
> lemon
>
> If cell A1 is "red" in my example, how can I autopopulate A2-A4 to also be
> "red" and A6-A7 to autopopulate with "yellow" based on that value in A5? Is
> there some formular expression that I could use. I'm not VisualBasic
> skilled, but could something simple be done there?

You could get rid of the blanks in column B where there is a new
colour by first of all inserting a new cell in A1, so all the colours
move down one row. Then you could apply autofilter to column B to
select Blanks. Then you could highlight all the visible rows within
the filter range (although there will be nothing in them - use the
blue row identifiers as a guide) and Edit | Delete Row. Select All
from the filter pull-down in column B, and you should be left with
this:


red apple
firetruck
wagon
yellow canary
lemon

Then select the cell below "red" within this data and press F5 (or
Edit | GoTo), click on Special then click on Current Region. Then
press F5 again, click Special, then click Blanks -all the blanks in
column A should be highlighted, with the active cell being under the
cell containing "red".

Then type = and then click on the cell containing "red", and then use
CTRL-Enter to put this formula in all the blanks.

You will then need to fix the values, so highlight all the data in
column A, click <copy>, then Edit | Paste Special | Values (check) |
OK then <Enter>.

Than should give you what you want without any VBA.

Hope this helps.

Pete


On Aug 10, 5:50 pm, Jay S. <Jay S...@discussions.microsoft.com> wrote:
> I'm trying to work with an export from a Lotus Notes db <grumble>. My first
> column contains sporadic entries (with blank cells between), as each of the
> column A entries are associated with column B entries in the rows where the
> column A cell is empty. For example:
>
> red
> apple
> firetruck
> wagon
> yellow
> canary
> lemon
>
> If cell A1 is "red" in my example, how can I autopopulate A2-A4 to also be
> "red" and A6-A7 to autopopulate with "yellow" based on that value in A5? Is
> there some formular expression that I could use. I'm not VisualBasic
> skilled, but could something simple be done there?

Haven't tried these yet but certainly will (and wanted to reply before the
weekend)! Thank you all for the suggestions.

"Jay S." wrote:

> I'm trying to work with an export from a Lotus Notes db <grumble>. My first
> column contains sporadic entries (with blank cells between), as each of the
> column A entries are associated with column B entries in the rows where the
> column A cell is empty. For example:
>
> red
> apple
> firetruck
> wagon
> yellow
> canary
> lemon
>
> If cell A1 is "red" in my example, how can I autopopulate A2-A4 to also be
> "red" and A6-A7 to autopopulate with "yellow" based on that value in A5? Is
> there some formular expression that I could use. I'm not VisualBasic
> skilled, but could something simple be done there?