Can I reference a cell based on value in other cell?

[bvandijk]

Hi there,


So for instance I have data in A1:A:10
I type a number in cell B1, say 3

Now I want cell C1 to contain A3+5

but when I chance B1 to 4, C1 should change to A4+5

in other words the fomulea in C1 has a reference that depends on another cell (a pointer if you wish).

Thanks heaps!
Bas

Hi

Try something like this in C1:
=INDIRECT("A"&B1)+5

Andy.

"bvandijk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
>
> So for instance I have data in A1:A:10
> I type a number in cell B1, say 3
>
> Now I want cell C1 to contain A3+5
>
> but when I chance B1 to 4, C1 should change to A4+5
>
> in other words the fomulea in C1 has a reference that depends on
> another cell (a pointer if you wish).
>
> Thanks heaps!
> Bas
>
>
> --
> bvandijk
> ------------------------------------------------------------------------
> bvandijk's Profile:
> http://www.excelforum.com/member.php...o&userid=32300
> View this thread: http://www.excelforum.com/showthread...hreadid=520570
>

[bvandijk]

Great ! it works.

[bvandijk]

It works great but I have another problem now: when the target cell is empty, the formuleau returns '0' instead of an empty cell.

Any hints on that?

Thanks!
Bas

Here you go:

=IF(INDIRECT("A"&B1)=0,"",INDIRECT("A"&B1))

Andy.

"bvandijk" <[email protected]> wrote in
message news:[email protected]...
>
> It works great but I have another problem now: when the target cell is
> empty, the formuleau returns '0' instead of an empty cell.
>
> Any hints on that?
>
> Thanks!
> Bas
>
>
> --
> bvandijk
> ------------------------------------------------------------------------
> bvandijk's Profile:
> http://www.excelforum.com/member.php...o&userid=32300
> View this thread: http://www.excelforum.com/showthread...hreadid=520570
>