Situation: I have a tab with 2 columns, A & B. A is either blank or contains a dash - that I manually add. B is a date.
Desired behavior: When I add a dash in column A I’d like the date to increment up 1 day in Col B of the same row. If A is blank then the date should be the same as row above it.
Current setup: right now I have a formula that works but I’d like to make it an ARRAYFORMULA (or BYROW, etc.) so I don’t need to drag my existing formula down an arbitrary length and potentially run past it in the future. The formula I’ve entered in B5 (which produces the desired outcome) and dragged down is:
=IF(A5="-",
OFFSET(B5,-1,0)+1,
OFFSET(B5,-1,0))
Problem: the above formula works perfectly but I have to drag it down which I’d like to avoid. I’ve tried the approaches below but each returns a “Circular Dependency” error. Is this where something like FLATTEN comes in?
Attempt 1:
=ARRAYFORMULA(IF(A5:A="-",
OFFSET(B5:B,-1,0)+1,
OFFSET(B5:B,-1,0))
Attempt 2:
=BYROW(A5:A,LAMBDA(r,IF(r="-",
OFFSET(r,-1,1)+1,
OFFSET(r,-1,1))
>Solution :
Assuming starting_date is manually given at Cell_B4
=scan(B4,A5:A,lambda(a,c,if(c="-",a+1,a)))
