Use index to get range for dget to use

[Adella]

I have a dget function that I want to use different range and criteria ranges
depending on a flag. I thought that index would work but I am getting a
value error. Thanks in advance for any help.

Here's the formula:

=dget(index(rangearray,1),az11,index(rangearray,3))

whrere
Med1_602
Med2_602
Med1_602Crit
Med2_602

are in a range called rangearray. These are all ranges that I want to use
in the dget formula depending on other criteria.

If I do the index function independently it gives me the correct range but
in the dget formula I get an error?

Thanks, I posted this last week on the function area, but didn't receive a
solution, hoping someone here know one.

Thanks in advance for any help.

 

[sebastienm]

Hi Adella

What i think happens is

The part INDEX(rangearray,1) returns the string (value) in rangearray
having index 1. The result is say "Med1_602". But this result is a String not
a range; you could even replace that specific cell by ="Med1_602", same
result. So, it's understood as a String and XL doesn't try to match it with
any Named Range.
That is you need to transform this string into a range having the same name.
You can use the INDIRECT function for that:

In C1:C3, enter the values 1, 2, 3
In A1, enter the string: "C1"
---> this is just the String "C1", not a range
---> the cell displays 'C1' and not the value in the
range C1
In A2, try: =INDIRECT(A1)
----> now it shows the value in C1
ie: get the value in the range corresponding to the
value in A1

Now with your INDEX function it would be
INDIRECT(index(rangearray),1))
---> index(rangearray),1 returns 'Med1_602'
---> then INDIRECT("Med1_602") returns the named range Med1_602

I hope this helps

 

[Adella]

Sebastienm,

This works great!

Thanks,

Adella

Hi Adella

What i think happens is

The part INDEX(rangearray,1) returns the string (value) in rangearray
having index 1. The result is say "Med1_602". But this result is a String not
a range; you could even replace that specific cell by ="Med1_602", same
result. So, it's understood as a String and XL doesn't try to match it with
any Named Range.
That is you need to transform this string into a range having the same name.
You can use the INDIRECT function for that:

In C1:C3, enter the values 1, 2, 3
In A1, enter the string: "C1"
---> this is just the String "C1", not a range
---> the cell displays 'C1' and not the value in the
range C1
In A2, try: =INDIRECT(A1)
----> now it shows the value in C1
ie: get the value in the range corresponding to the
value in A1

Now with your INDEX function it would be
INDIRECT(index(rangearray),1))
---> index(rangearray),1 returns 'Med1_602'
---> then INDIRECT("Med1_602") returns the named range Med1_602

I hope this helps