conditional cell fill above & below

[andyLLL]

Hi I have run into a tricky situation where i need to fill 10 cells above and below a cell containing content. i.e. within a set of 5000 rows 100 may have content in a cell for each of these 100 i would like to be able to copy the contents of that cell 10 up and 10 down. Has anyone experience with doing something like this?

 

[Automatrix]

Not pretty, but this will fill in 3 cells up and down, just add another 7 columns. It runs into trouble if the ranges overlap.

Book2
	A	B	C	D	E
2	1				A
3	2			A	A
4	3		A	A	A
5	4	A	A	A	A
6	5		A	A	A
7	6			A	A
8	7				A
9	8
10	9				B
11	10			B	B
12	11		B	B	B
13	12	B	B	B	B
14	13		B	B	B
15	14			B	B
16	15				B
17	16
18	17
19	18				C
20	19			C	C
21	20		C	C	C
22	21	C	C	C	C
23	22		C	C	C
24	23			C	C
25	24				C
Sheet2

Cell Formulas
Range	Formula
C2:E25	C2	=IF(B2<>"",B2,IF(B3<>"",B3,IF(B1<>"",B1,"")))

 

[andyLLL]

Thanks. That’s really helpful.