Using a value in a cell as a Reference in a formula?

[NateBuckley]

Hello everyone.

I'm just messing around on Excel, and I wish to use a cell value to be used
in a formula so E.G

B Column is full of random Words (spanner, spoon, stool etc)

Column A1 = 1

I want in Cell C1 to do something like =Sheet1!B & Sheet1!A1

I know the above doesn't work, but it would basically I could change the
number in cell A1 and it would look at a different row in Column B.

I hope that makes sense, thanks in advance.

Note - I was thinking something to do with INDIRECT, but can't seem to get
that right either.

Nate

 

[Don Guillett]

=INDIRECT("B"& a1)

 

[Mike H]

Hi,

You were very close and had yo persevered I think you would have got it. Try
this

=INDIRECT("sheet1!B"&A1)

Indirect is treated as text but in this case there is a second non-text bit
the address A1

Mike

 

[NateBuckley]

Cheers it does indeed work.

Thanks alot for your help.

Many thanks.

 

[Rick Rothstein \(MVP - VB\)]

The INDIRECT function is volatile; here is a non-volatile solution...

=INDEX(B1:B1000,A1)

Rick