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

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

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

=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

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

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

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

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

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

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