# cell ref in a formula (Excel 2010, Windows)

#### pico

##### New Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi pico
Welcome to the board

Try in E3:

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

Copy to the other cells.

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!!

Replies
12
Views
430
Replies
1
Views
302
Replies
0
Views
151
Replies
3
Views
165
Replies
4
Views
197

1,207,110
Messages
6,076,604
Members
446,216
Latest member
BEEALTAIR

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back