Referencing consecutive cells when using merged blocks of cells

[neilos81]

Hi all

Not really sure how to describe this hence the rubbish title (!!) but should be fairly easy from the picture. I have data in column B, in this example, letters starting at 'a'. I want to reference these in in merged celled (column D) but want the second 'block' down to show 'b', not 'e' as it currently does, due the 4 rows being added on to the cell reference. I need to then be able to copy and paste the merged blocks so they reference consecutive cells from column B. I've had a play with the offset function but have been unsuccessful so far

Any help would be appreciated!

Thanks

 

[James006]

Hi,

If you are using =B4 in cell D4 ... woudn't you get the correct answer by simply copying it down ...?

 

[kevin9999]

Book1
	A	B	C	D
3
4		a		a
5		b
6		c
7		d
8		e		b
9		f
10		g
11		h
12		i		c
13		j
14		k
15		l
16		m		d
17		n
18		o
19		p
20		q
21
Sheet2

Cell Formulas
Range	Formula
D8,D12,D16	D8	=IFERROR(INDEX($B$4:$B$20,MATCH(D4,$B$4:$B$20,0)+1),"")

Book1
	A	B	C	D
3
4		a		g
5		b
6		c
7		d
8		e		h
9		f
10		g
11		h
12		i		i
13		j
14		k
15		l
16		m		j
17		n
18		o
19		p
20		q
21
Sheet2

Cell Formulas
Range	Formula
D8,D12,D16	D8	=IFERROR(INDEX($B$4:$B$20,MATCH(D4,$B$4:$B$20,0)+1),"")

 

[neilos81]

Hi,

If you are using =B4 in cell D4 ... woudn't you get the correct answer by simply copying it down ...?

Hi

That's what I did originally but when copying it down, D8 then refers to B8, hence the 'e'

 

[neilos81]

Book1
	A	B	C	D
3
4		a		a
5		b
6		c
7		d
8		e		b
9		f
10		g
11		h
12		i		c
13		j
14		k
15		l
16		m		d
17		n
18		o
19		p
20		q
21
Sheet2

Cell Formulas
Range	Formula
D8,D12,D16	D8	=IFERROR(INDEX($B$4:$B$20,MATCH(D4,$B$4:$B$20,0)+1),"")

Book1
	A	B	C	D
3
4		a		g
5		b
6		c
7		d
8		e		h
9		f
10		g
11		h
12		i		i
13		j
14		k
15		l
16		m		j
17		n
18		o
19		p
20		q
21
Sheet2

Cell Formulas
Range	Formula
D8,D12,D16	D8	=IFERROR(INDEX($B$4:$B$20,MATCH(D4,$B$4:$B$20,0)+1),"")

Thanks! I'll give this a go.

 

[James006]

Sorry ... read too quickly your request

=OFFSET($B$4,INT(INT(ROW()-4)/4),0)