Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and want
to read and show the data in A(the number in B2) or A25 in this case, but it
comes up with #name?. Am I doing it right?

Try something like this:

=INDEX(A:A,B2)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"DrB" wrote in message
...
Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and want
to read and show the data in A(the number in B2) or A25 in this case, but
it comes up with #name?. Am I doing it right?

One way:

=INDIRECT("A" & B2)

Another:

=INDEX(A:A,B2)



In article ,
"DrB" wrote:

Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and want
to read and show the data in A(the number in B2) or A25 in this case, but it
comes up with #name?. Am I doing it right?

=INDIRECT("A"&B2)

with 25 in B2 will return what's in A25, a non volatile version would be

=INDEX(A:A,B2)


--


Regards,


Peo Sjoblom



"DrB" wrote in message
...
Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and want
to read and show the data in A(the number in B2) or A25 in this case, but
it comes up with #name?. Am I doing it right?

Try it like this:

=INDIRECT("A"&B2)

Or possibly:

=INDEX(A:A,B2)

--
Biff
Microsoft Excel MVP


"DrB" wrote in message
...
Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and want
to read and show the data in A(the number in B2) or A25 in this case, but
it comes up with #name?. Am I doing it right?

No. You need to do it this way:

=INDIRECT("A"&B2)

i.e. the value in B2 (25) is joined onto A to give the cell reference,
which INDIRECT can make sense of.

Hope this helps.

Pete

On Oct 15, 11:29 pm, "DrB" wrote:
Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and want
to read and show the data in A(the number in B2) or A25 in this case, but it
comes up with #name?. Am I doing it right?

No, as you have it, Excel is looking for a function called A. Try using the
INDIRECT function:

=INDIRECT("A"&B2)

HTH,
Elkar


"DrB" wrote:

Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and want
to read and show the data in A(the number in B2) or A25 in this case, but it
comes up with #name?. Am I doing it right?

That works. Thanks to everyone.

Now I want to paste into cells but keep B2 the same and allow A to change as
I paste along the row. I tried =indirect(A&"B2") but did not work. Can it be
done?

"Elkar" wrote in message
...
No, as you have it, Excel is looking for a function called A. Try using
the
INDIRECT function:

=INDIRECT("A"&B2)

HTH,
Elkar


"DrB" wrote:

Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and
want
to read and show the data in A(the number in B2) or A25 in this case, but
it
comes up with #name?. Am I doing it right?

Pasting along a row, across columns - just use the Index() formula with an
absolute revision to B2:

=INDEX(A:A,$B$2)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
\
"DrB" wrote in message
...
That works. Thanks to everyone.

Now I want to paste into cells but keep B2 the same and allow A to change
as I paste along the row. I tried =indirect(A&"B2") but did not work. Can
it be done?

"Elkar" wrote in message
...
No, as you have it, Excel is looking for a function called A. Try using
the
INDIRECT function:

=INDIRECT("A"&B2)

HTH,
Elkar


"DrB" wrote:

Trying to have a cell read the number in another cell to come up with
the
cell number from which the data is located, i.e., I have 25 in B2 and
want
to read and show the data in A(the number in B2) or A25 in this case,
but it
comes up with #name?. Am I doing it right?

"DrB" wrote
Now I want to paste into cells but keep B2 the same and allow A to change
as I paste along the row...

Use Biff's 2nd option, with the point to B2 fixed
In the starting cell: =INDEX(A:A,$B2)
Copy across
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

That get's a bit more complex, but see if this works:

=INDIRECT(IF(COLUMN()26,CHAR(INT((COLUMN()-1)/26)+64)&CHAR(MOD(COLUMN()+25,26)+65),CHAR(COLUMN() +64))&$B$2)

HTH,
Elkar


"DrB" wrote:

That works. Thanks to everyone.

Now I want to paste into cells but keep B2 the same and allow A to change as
I paste along the row. I tried =indirect(A&"B2") but did not work. Can it be
done?

"Elkar" wrote in message
...
No, as you have it, Excel is looking for a function called A. Try using
the
INDIRECT function:

=INDIRECT("A"&B2)

HTH,
Elkar


"DrB" wrote:

Trying to have a cell read the number in another cell to come up with the
cell number from which the data is located, i.e., I have 25 in B2 and
want
to read and show the data in A(the number in B2) or A25 in this case, but
it
comes up with #name?. Am I doing it right?

"DrB" wrote...
That works. Thanks to everyone.

Now I want to paste into cells but keep B2 the same and allow A to
change as I paste along the row. I tried =indirect(A&"B2") but did
not work. Can it be done?
....

Your formula =INDIRECT(A&"B2") doesn't work unless A is a defined name
that evaluates to a text string, perhaps to "XYZ", in which case
A&"B2" would evaluate to "XYZB2", and that'd have to be a different
defined name referring to a cell range.

If you want the column letter to vary along the row given by the value
of cell B2, then you need to use something like either

=INDEX($1:$65536,$B$2,x)

or

=INDIRECT("R"&$B$2&"C"&x,0)

where x is a placeholder for an expression that determines how you
want the column to vary.

Oops, sorry, line
Use Biff's 2nd option ..

should have read as:
Use the INDEX option suggested in Ron's, JE's, Peo's & Biff's responses ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"DrB" wrote...
That works. Thanks to everyone.

Now I want to paste into cells but keep B2 the same and allow A to
change as I paste along the row. I tried =indirect(A&"B2") but did
not work. Can it be done?
....

Your formula =INDIRECT(A&"B2") doesn't work unless A is a defined name
that evaluates to a text string, perhaps to "XYZ", in which case
A&"B2" would evaluate to "XYZB2", and that'd have to be a different
defined name referring to a cell range.

If you want the column letter to vary along the row given by the value
of cell B2, then you need to use something like either

=INDEX($1:$65536,$B$2,x)

or

=INDIRECT("R"&$B$2&"C"&x,0)

where x is a placeholder for an expression that determines how you
want the column to vary.

Thanks everyone for your help

"DrB" wrote in message
...
That works. Thanks to everyone.

Now I want to paste into cells but keep B2 the same and allow A to change
as I paste along the row. I tried =indirect(A&"B2") but did not work. Can
it be done?

"Elkar" wrote in message
...
No, as you have it, Excel is looking for a function called A. Try using
the
INDIRECT function:

=INDIRECT("A"&B2)

HTH,
Elkar


"DrB" wrote:

Trying to have a cell read the number in another cell to come up with
the
cell number from which the data is located, i.e., I have 25 in B2 and
want
to read and show the data in A(the number in B2) or A25 in this case,
but it
comes up with #name?. Am I doing it right?