Dynamic cell refrence

[z0de]

In a cell a user types a date, in another cell the match function is used to work out what row that date is on in another sheet. I want another cell now to use that row number as the row for a final function. e.g.


in cell b4: 23/05/2011

In cell c4: =(MATCH(B4,Dates,0)+1) which returns a 2, the row that the date is on.

In cell d4: =b(VALUE(C4)) which should become =b2 and in turn output the value of cell b2 on the sheet. This the formula I can not get to work.

 

[wigi]

1 formula will suffice:

=INDEX(cellsincolumnB,MATCH(B4,Dates,0)+1)

 

[Andrew Poulsom]

Like this?

=INDIRECT("B" & C4)

 

[z0de]

Thanks for the responses, indirect worked fine but the index approach gave a #NAME? error. I'm not sure cellsincolumnB was recognised.