I have a somewhat intersting problem that I'm having trouble solving.
Consider the following simplified spreadsheet:

A B
1 2
2 B 55
3 =A2&A1

Now, the formula in A3 will become the string "B2." What I want is to
be able to enter a formula in another cell, give it the string from
A3, and have the value of that cell be 55. Is there a function that
takes a string representing the position of a cell that returns that
cell's value?

(I know that this seems really dumb with the simplified example, but I
need to use this for something more complex).

Thanks,
Ben

Try this:

=INDIRECT(A3)

Returns 55

Or, you can do this:

=INDIRECT(A2&A1)

--
Biff
Microsoft Excel MVP


wrote in message
...
I have a somewhat intersting problem that I'm having trouble solving.
Consider the following simplified spreadsheet:

A B
1 2
2 B 55
3 =A2&A1

Now, the formula in A3 will become the string "B2." What I want is to
be able to enter a formula in another cell, give it the string from
A3, and have the value of that cell be 55. Is there a function that
takes a string representing the position of a cell that returns that
cell's value?

(I know that this seems really dumb with the simplified example, but I
need to use this for something more complex).

Thanks,
Ben

In C1 enter =INDIRECT(A3)

Or simply =INDIRECT(A2&A1) which cuts out the middleman.


Gord Dibben MS Excel MVP

On Mon, 19 Nov 2007 19:13:21 -0800 (PST), wrote:

I have a somewhat intersting problem that I'm having trouble solving.
Consider the following simplified spreadsheet:

A B
1 2
2 B 55
3 =A2&A1

Now, the formula in A3 will become the string "B2." What I want is to
be able to enter a formula in another cell, give it the string from
A3, and have the value of that cell be 55. Is there a function that
takes a string representing the position of a cell that returns that
cell's value?

(I know that this seems really dumb with the simplified example, but I
need to use this for something more complex).

Thanks,
Ben

On Nov 19, 7:27 pm, "T. Valko" wrote:
Try this:

=INDIRECT(A3)

Ah, thanks, Biff... that works great. Although now I have another
question... what if the cell that I want to reference is on another
sheet? Excel seems to be choking on something like AnotherSheet!
INDIRECT(A3).

Is there a way to do this?

Thanks again!

-Ben

INDIRECT("AnotherSheet!"&A3)


" wrote:

On Nov 19, 7:27 pm, "T. Valko" wrote:
Try this:

=INDIRECT(A3)

Ah, thanks, Biff... that works great. Although now I have another
question... what if the cell that I want to reference is on another
sheet? Excel seems to be choking on something like AnotherSheet!
INDIRECT(A3).

Is there a way to do this?

Thanks again!

-Ben

INDIRECT("AnotherSheet!"&A3)

or
INDIRECT("'Another Sheet'!"&A3)
if there are spaces in your sheet name (note the apostrophes ' )


" wrote:

On Nov 19, 7:27 pm, "T. Valko" wrote:
Try this:

=INDIRECT(A3)

Ah, thanks, Biff... that works great. Although now I have another
question... what if the cell that I want to reference is on another
sheet? Excel seems to be choking on something like AnotherSheet!
INDIRECT(A3).

Is there a way to do this?

Thanks again!

-Ben