Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Michael Sharpe
 
Posts: n/a
Default 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
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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



  #3   Report Post  
 
Posts: n/a
Default

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


  #4   Report Post  
Junior Member
 
Posts: 1
Default

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

Rgds,
Murali
Quote:
Originally Posted by View Post
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"