Passing a range name as an argument to the Index Function
 

Hello,
I hope you can help me - this is driving me mad. I am attempting to
return a value using the Index function. The first argument of this
function is the range from which you wish to pick your value. I have
multiple ranges on one worksheet which I have named eg MsFeMnDr. By
concatenating certain inputs that the user enters I build the name of
the range from which I wish to pull my data. Say this value is stored
in cell T3 (ie cell T3 contains the string MsFeMnDr). If I then try
and call my value using =Index(T3,6,7) it returns #ref!. Inputting
=Index(MsFeMnDr,6,7) will return the value that I want but I want to
make the range name dynamic. I can see in the first example that
excel is looking at the cell T3 in isolation as my range and not the
range to which the string in this cell refers to. Can anyone tell me
if there is a simple way to do this? Thanks for your help. Michael

Michael,

I believe so

=Index(INDIRECT(T3),6,7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael Sharpe" wrote in message
om...
Hello,
I hope you can help me - this is driving me mad. I am attempting to
return a value using the Index function. The first argument of this
function is the range from which you wish to pick your value. I have
multiple ranges on one worksheet which I have named eg MsFeMnDr. By
concatenating certain inputs that the user enters I build the name of
the range from which I wish to pull my data. Say this value is stored
in cell T3 (ie cell T3 contains the string MsFeMnDr). If I then try
and call my value using =Index(T3,6,7) it returns #ref!. Inputting
=Index(MsFeMnDr,6,7) will return the value that I want but I want to
make the range name dynamic. I can see in the first example that
excel is looking at the cell T3 in isolation as my range and not the
range to which the string in this cell refers to. Can anyone tell me
if there is a simple way to do this? Thanks for your help. Michael

Thanks Bob, that worked a treat,

Regards,
Michael

Bob Phillips wrote:
Michael,

I believe so

=Index(INDIRECT(T3),6,7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael Sharpe" wrote in message
om...
Hello,
I hope you can help me - this is driving me mad. I am attempting
to
return a value using the Index function. The first argument of
this
function is the range from which you wish to pick your value. I
have
multiple ranges on one worksheet which I have named eg MsFeMnDr.
By
concatenating certain inputs that the user enters I build the name
of
the range from which I wish to pull my data. Say this value is
stored
in cell T3 (ie cell T3 contains the string MsFeMnDr). If I then
try
and call my value using =Index(T3,6,7) it returns #ref!. Inputting
=Index(MsFeMnDr,6,7) will return the value that I want but I want
to
make the range name dynamic. I can see in the first example that
excel is looking at the cell T3 in isolation as my range and not
the
range to which the string in this cell refers to. Can anyone tell
me
if there is a simple way to do this? Thanks for your help. Michael

Thanks Michael & Bob
I almost became mad while trying to compute tax...
Thanks to both of you for the info.

Rgds,
Murali