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 F5SpecialBlanksOK

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
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 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?