Evasive Dynamic Formula

[DougRobertson]

Hello,

From cell D10, I want to have a formula that tells me the value of the number in the cell two rows up, or in this case, cell D8.

For a non-dynamic formula, I would just use:

=D8

But I want the row number to be dynamic, something like

=D&(Row()-2)

I fear that the solution is painfully simple, but I can't seem to find it!

With thanks in advance!

~ DWR

 

[Jonmo1]

Try

=INDEX(D:D,ROW()-2)

 

[DougRobertson]

Try

=INDEX(D:D,ROW()-2)

We're getting close:

Whereas the formula

If(D10="","Blank","NotBlank")

could return a "" (empty value), which I need, the INDEX route does not.

Is there another way around this?

Thanks again!

 

[Jonmo1]

Why doesn't that work?

the index function is simply a replacement for the cell reference only, not the whole formula.

So take your formula
If(D10="","Blank","NotBlank")

You replace the 'Cell Reference' only, with the index function
If(INDEX(D:D,ROW()-2)="","Blank","NotBlank")

 

[DougRobertson]

Hmmm - it was actually another part of the bigger formula I'm working on that was the problem.

This was a great help my friend!!

Thank-you very much for sharing your expertise!

~ DWR