I have a very large spreadsheet where 1 column is a folder path. One path
will be listed, then a random number of blank cells are below it until a
different path is found. What I'm looking for is a command to make the path
fill down into the empty cells below it until it finds a new value, then fill
that value down, etc.

If this is a one-time effort, Rose, it's not hard. Say your paths are in
column 4. In a helper column to the right use this formula:

=IF(RC4="",R[-1]C4,RC4)

If you're an A1 monoglot, that looks like this in A1 format:

=IF(D2="",D1,D2)

This fills into the helper column the actual value in col 4, or the value
from the above row if col 4 is blank. Once you have these, just copy the
result into col 4 using Paste.Special.Values.

--- "Rose" wrote:
I have a very large spreadsheet where 1 column is a folder path. One path
will be listed, then a random number of blank cells are below it until a
different path is found. What I'm looking for is a command to make the path
fill down into the empty cells below it until it finds a new value, then fill
that value down, etc.

That only works for the row that has the path (because it returns a false)
and the following row (which returns a true). After that, the 3rd row is
looking for what's in the 4th column on the above (2nd) row, which is still
blank, so it returns a blank.


"Bob Bridges" wrote:

If this is a one-time effort, Rose, it's not hard. Say your paths are in
column 4. In a helper column to the right use this formula:

=IF(RC4="",R[-1]C4,RC4)

If you're an A1 monoglot, that looks like this in A1 format:

=IF(D2="",D1,D2)

This fills into the helper column the actual value in col 4, or the value
from the above row if col 4 is blank. Once you have these, just copy the
result into col 4 using Paste.Special.Values.

--- "Rose" wrote:
I have a very large spreadsheet where 1 column is a folder path. One path
will be listed, then a random number of blank cells are below it until a
different path is found. What I'm looking for is a command to make the path
fill down into the empty cells below it until it finds a new value, then fill
that value down, etc.

Select the column with the blanks then F5SpecialBlanksOK

Type an = sign in active blank cell then point or arrow up to cell above and
hit CTRL + ENTER to fill blanks.

CopyPaste SpecialValuesOKEsc


Gord Dibben MS Excel MVP

On Mon, 23 Mar 2009 11:44:09 -0700, Rose
wrote:

I have a very large spreadsheet where 1 column is a folder path. One path
will be listed, then a random number of blank cells are below it until a
different path is found. What I'm looking for is a command to make the path
fill down into the empty cells below it until it finds a new value, then fill
that value down, etc.

That was it - thank you!

"Gord Dibben" wrote:

Select the column with the blanks then F5SpecialBlanksOK

Type an = sign in active blank cell then point or arrow up to cell above and
hit CTRL + ENTER to fill blanks.

CopyPaste SpecialValuesOKEsc


Gord Dibben MS Excel MVP

On Mon, 23 Mar 2009 11:44:09 -0700, Rose
wrote:

I have a very large spreadsheet where 1 column is a folder path. One path
will be listed, then a random number of blank cells are below it until a
different path is found. What I'm looking for is a command to make the path
fill down into the empty cells below it until it finds a new value, then fill
that value down, etc.