Get row position reference and fill down X number of times until you encounter a new value

1
votes

In Google Sheets, I have a column that is randomly populated with a value: 

1  value
2  
3  
4  value
5  value
6  
7  
8  
9  value
10

I need to populate all the blank, non-value cells in the column with a reference to the row number of the value above it.

Among many other things, I've tried using an IF statement:

IF=A2="",row(A1),row(A2)

This would look for a blank value in column A, give me a row reference if the value IS blank, and give me the value of the cell itself if it is not blank. However, this does not work when I fill down.

I am looking for a formula that will look at the values in column A and give me a row ref for the most recent appearance of value in that column: 

1  value
2  1
3  1
4  value
5  value
6  5
7  5
8  5
9  value
10  9
1
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
what is your formula for "randomly printing value"? – player0
No formula, I'm staging objects for ingest into a repository and each parent object has a variable number of children. – Peter

1 Answers

1
votes
=ARRAYFORMULA(IF(A1:A<>"", A1:A, 
 IF(ROW(A1:A) <= MAX(IF(NOT(ISBLANK(A1:A)), ROW(A1:A))),
 VLOOKUP(ROW(A1:A), FILTER(ROW(A1:A), LEN(A1:A)), 1), )))

0