3

I'm trying to populate a column (Cell C2) with the value of the cell in the column to its left (Cell B2) if B2 is not empty; if B2 is empty, then C2 equals to C1.

If B3 is not empty, then C3 equals to B3; if B3 is empty, C3 equals C2.

I try using an array formula, but it returns circular reference error. And I certainly can't use INDEX and INDIRECT either.

Please help.

The sample file:

https://docs.google.com/spreadsheets/d/1TXz5m5LtTF632bMrwIIDlO-4NMavgs6HA4FE8_BFhRs/edit?usp=sharing

player0
  • 124,011
  • 12
  • 67
  • 124
CelestialBeing
  • 37
  • 3

2 Answers2

2

paste in C3 cell:

=ARRAYFORMULA(IF(D3:D<>"", IF(ROW(B3:B) <= MAX(IF(D3:D<>"", ROW(B3:B))),
 TEXT(VLOOKUP(ROW(B3:B), FILTER({ROW(B3:B), B3:B}, LEN(B3:B)), 2), 
 "dd mmm yyyy"), ), ))

0

player0
  • 124,011
  • 12
  • 67
  • 124
1

You can use vlookup on the row number:

=ArrayFormula(to_date(if(D3:D="","",vlookup(row(A3:A),if(B3:B<>"",{row(A3:A),B3:B}),2,true))))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37