cell ref in a formula (Excel 2010, Windows)

[pico]

I have the following table:

	D	E	F	G	H
3	10.0.0.0	refers to D3	refers to D3	refers to D3	refers to D3
4		refers to D3	refers to D3	refers to D3	refers to D3
5		refers to D3	refers to D3	refers to D3	refers to D3
6		refers to D3	refers to D3	refers to D3	refers to D3
8	10.0.1.0	refers to D8	refers to D8	refers to D8	refers to D8
9		refers to D8	refers to D8	refers to D8	refers to D8
10		refers to D8	refers to D8	refers to D8	refers to D8
11		refers to D8	refers to D8	refers to D8	refers to D8

<tbody>
</tbody>

The cells which say "refers to D#" have a formula which need to refer to the value in the top left of the range which consists of 4 rows x 9 cols including col D (only 4 cols, E-H shown here for brevity).

I want to use the same formula for all blocks in rows 1-n. I have only come up with two possibilities

1. copy the value in D3 to D4:D6, and the value in D8 to D9:D11, etc., and in the formula use an absolute reference to col $D# so that the formula in E3 refers to $D3, E4 refers to $D4, etc. I can copy the formula to any cell in the block and it will still work no matter what cell I'm in within the 4-row block
2. use an absolute reference for cells within a 4-row block:
   E3-H6 refers to $D$3
   E8-H11 refers to $D$8

Both of these require edits either to the:

• worksheet (duplicate info in col D for each block and using $D# in the formula) or
• the formula (editing the absolute reference for each block).

Is there a way to create a formula which will always refer to the top left cell without naming each block? I looked at INDIRECT, named ranges, lookups, MATCH, but nothing seems to jump out as the right solution. The position of the top left cell will vary depending on the row/column. For row 3, col E, it is 1 col to the left. For col F, it is 2 cols to the left, etc. For row 4, it is 1 row up and 1 col to the left, etc.

 

[pgc01]

Hi pico
Welcome to the board

Try in E3:

=LOOKUP(2,1/($D$3:$D3<>""),$D$3:$D3)

Copy to the other cells.

 

[pico]

awesome!! works!!! now I need to study and ask some questions about how and why it works; I thought mod() or something like it would help, and this is the "something like it" formula I was looking for.

Thank You!!