I would like to do something similar to what was solved in the question:An arrayformula to find the previous non-empty cell in another columnThe problem was:If I have a column input, I would like the column ouput to be filled with the preceding value of the input column.input - ouputa        a         a         ab        b         b         b         bc        c         c         c         cIt was a great answer, however, the ArrayFormula works with an user-specified range:=ArrayFormula(vlookup(ROW(1:10),{IF(LEN(A1:A10)>0,ROW(1:10),),A1:A10},2))How would I make the function to automatically get the last row that it has to take?Specifically, I would like to know how to get automatically the number 10 of the last function.I have tried to use INDIRECT but it finds an error.=ArrayFormula(vlookup(ROW(INDIRECT(B3:B&INDEX(SORT(B:B,ROW(B:B),FALSE),1))),{IF(LEN(INDIRECT(B3:B&INDEX(SORT(B:B,ROW(B:B),FALSE),1))>0,ROW(INDIRECT(3:&INDEX(SORT(B:B,ROW(B:B),FALSE),1)),),INDIRECT(B3:B&INDEX(SORT(B:B,ROW(B:B),FALSE),1)},2)))))This part: INDIRECT(B3:B&INDEX(SORT(B:B,ROW(B:B),FALSE),1))is used to get the last value of a column.I have created a spreadsheet with the functions:https://docs.google.com/spreadsheets/d/15jbXZbixRYxJSRQuKgBU8BWN6KYNjwwT5PzvYp986Ww/edit#gid=0Any help would be appreciated.
An arrayformula to find the previous non-empty cell in another column and find the last row to work with
google spreadsheets;formulas
Can't edit your spreadsheet, but this formula (entered in C3)=ArrayFormula(if(row(B3:B) <= max(if(not(isblank(B3:B)), row(B3:B))),vlookup(row(B3:B),filter({row(B3:B),B3:B},len(B3:B)),2),))should bring you the output you expected.