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
  • 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 '14 at 21:38
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),))

  • 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 '14 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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