Fill with above cell if cell is empty on large range

[Mooncake1]

hi friends!

does anyone know how to use a spill formula to fill blank cells with the row above's data?
i have some really long data sets, and i am limited to in cell formula (excel online).
preferable i would like to add this to a range like " a1:index(a:a,endofdata) " where end of data is 50,000-100,000.
at the moment im doing the following, which is clunky:

book1
	A	B
1	Data:	Fdata:
2	dsgf	dsgf
3	gfh	gfh
4		gfh
5		gfh
6	gf	gf
7	ghj	ghj
8		ghj
9		ghj
10		ghj
11		ghj
12		ghj
13		ghj
14	fgh	fgh
15		fgh
16		fgh
17		fgh
18		fgh
19	46f	46f
20		46f
21	fg	fg
22	h	h
23	gf	gf
24	j	j
25	gj	gj
26		gj
27		gj
28	gf	gf
29		gf
30		gf
31		gf
32	j	j
33		j
34		j
35		j
36	kiu	kiu
37		kiu
38	erytrt	erytrt
Sheet1

Cell Formulas
Range	Formula
B2:B38	B2	=IF((A2)="",B1,A2)

 

[jasonb75]

at the moment im doing the following, which is clunky

It's actually simple and effective. Attempting to do the same with a formula that can spill would be clunky and inefficient.

 

[Mooncake1]

It's actually simple and effective. Attempting to do the same with a formula that can spill would be clunky and inefficient.

I'm doing this for 100,000 rows, using non-spilling formula makes the filesize 10x larger.
If it uses "endofdata" a spill formula will only need to calculate until the end of the data, not whole column

 

[jasonb75]

If it uses "endofdata" a spill formula will only need to calculate until the end of the data, not whole column

It doesn't need to do the whole column if you don't drag beyond the end of the data.

Also, you will note in my earlier reply that I said 'attempting to do the same' rather than 'doing the same'. Formulas that use progressive ranges are notoriously spill resistant, those that do spill often give incorrect results.

One key factor appears to be that a spilled formula can not read the results of other cells in the spill range correctly. For example, where you have consecutive blanks a spilled formula would not be able to get the value from the cell above that has already been spilled.

In the event that it is possible to find a spill formula that works, you would likely be saving on file size at an increased cost of processing effort due to the complexity of the formula needed.

 

[Mooncake1]

It doesn't need to do the whole column if you don't drag beyond the end of the data.

Also, you will note in my earlier reply that I said 'attempting to do the same' rather than 'doing the same'. Formulas that use progressive ranges are notoriously spill resistant, those that do spill often give incorrect results.

One key factor appears to be that a spilled formula can not read the results of other cells in the spill range correctly. For example, where you have consecutive blanks a spilled formula would not be able to get the value from the cell above that has already been spilled.

In the event that it is possible to find a spill formula that works, you would likely be saving on file size at an increased cost of processing effort due to the complexity of the formula needed.

the data is not static either, so it will need to be filled down manually each time.
I would much prefer a high processing power formula than a huge excel file that takes forever to load

 

[jasonb75]

What you are asking for appears to be impossible with a spillable formula (as I originally suspected). The only thing that comes close to working needs to be recalculated multiple times in order to return the correct results, the number of recalculations will be variable according to the actual data set it is being used with.

Unless you can use a full version of excel where vba is an option, you're going to be stuck with a regular formula filled down manually.