=A(B2) isn't working

  • Thread starter Thread starter DrB
  • Start date Start date
D

DrB

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)
 
=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
 
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
 
No, as you have it, Excel is looking for a function called A. Try using the
INDIRECT function:

=INDIRECT("A"&B2)

HTH,
Elkar
 
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?
 
Pasting along a row, across columns - just use the Index() formula with an
absolute revision to B2:

=INDEX(A:A,$B$2)
 
DrB said:
Now I want to paste into cells but keep B2 the same and allow A to change
as I paste along the row...
Click to expand...

Use Biff's 2nd option, with the point to B2 fixed
In the starting cell: =INDEX(A:A,$B2)
Copy across

---
 
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 said:
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?
Click to expand...
....

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.
 
DrB said:
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?
Click to expand...
....

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 said:
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?
Click to expand...
 
Back
Top