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

=INDIRECT("B"& a1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NateBuckley" wrote in message
...
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

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" wrote:

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

Cheers it does indeed work.

Thanks alot for your help.

Many thanks.

"Mike H" wrote:

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" wrote:

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

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

=INDEX(B1:B1000,A1)

Rick


"NateBuckley" wrote in message
...
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