Hello,

I have a large list of customer data that I would like to sort, but each
line does not contain a customer name, which is the field I want to sort by.
The spreadsheet shows a customer name on one line and then the various
invoices below it.

I am trying to write a formula that can do the following:

Move two cells to the right, and then return the first cell above this cell
that contains a name (the customer name). The data looks like this:

Customer ABC (2 cells to the right of the
Formula)
Blank cell Invoice # 1
Blank cell Invoice #2

In other words, the formula would be in the far left cell (now the blank
cell) and move 2 cells to the right and then return the "Customer ABC" cell.
But, I need a formula that I can copy down many rows of invoices that will
all return the same customer name.

I hope I have explained this well enough. Any help is much appreciated.

John M

In A1 add this array formula and copy down

=IF(C1<"",C1,INDEX($C$1:C1,MAX(IF($C$1:C1<"",ROW ($C$1:C1)))))

as an array formula, commit with Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John M" wrote in message
...
Hello,

I have a large list of customer data that I would like to sort, but each
line does not contain a customer name, which is the field I want to sort
by.
The spreadsheet shows a customer name on one line and then the various
invoices below it.

I am trying to write a formula that can do the following:

Move two cells to the right, and then return the first cell above this
cell
that contains a name (the customer name). The data looks like this:

Customer ABC (2 cells to the right of the
Formula)
Blank cell Invoice # 1
Blank cell Invoice #2

In other words, the formula would be in the far left cell (now the blank
cell) and move 2 cells to the right and then return the "Customer ABC"
cell.
But, I need a formula that I can copy down many rows of invoices that will
all return the same customer name.

I hope I have explained this well enough. Any help is much appreciated.

John M