0

How do you fill cells in column B (using an array formula) that are preceding the cell in column A with the value of column A only when cell in column C is not blank?

In other words, after column C is filled with text values, in the next empty row I enter a text value in column A, then I want the preceding cells in column B where column C is not empty to be filled with this manually entered value in column A, like this:

A   B   C
    d   notblank
    d   notblank
    d   notblank
d
    a   notblank
    a   notblank
    a   notblank
    a   notblank
a
    e   notblank
    e   notblank
e
Improve this question
1
  • 1
    First, in a blank sheet, I enter values in column C manually let’s say into three rows. When finnished, in the next row I enter a value in column A also manually (“d” in example). Now this value should be automatically entered in the preceding cells in column B if there is a value in the adjacent cell in column C. I’d like to use an array formula for this in column B this way the formula can expand as more rows added. Then this automatic fill should be repeated when I enter new rows in column C and then a value in column A (“a” in example).
    – komlos
    Sep 22, 2014 at 21:38

1 Answer 1

Reset to default
1

Assuming the data starts in row 1, try in B1:

=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),))

Out of interest, the formula simplifies a bit if you are retrieving from cells below:

=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))

Improve this answer
1
  • 1
    You did it agan, thank you! It works fine. The only thing that if you use a different regional setting then you need to change commas to semicolons ";", and one comma to a backslash "\" in the middle of the first formula between the right and left curly braces. This: "},{" to this: "}\{".
    – komlos
    Sep 23, 2014 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.