We help IT Professionals succeed at work.

Excel - indirect ranges intersection not working

625 Views
Last Modified: 2012-08-14
Hello,

I've been playing around with the indirect function and intersections a little but got to a point where I can't seem to make it work.

I have the following situation:
=INDEX((INDIRECT(mi_field) mi_f1),B61)  - works very well (returns the value of the first cell in the intersection

=INDEX((INDIRECT(mi_field) INDIRECT(Z47)),B61) - Dosen't work :(((( even if Z47 = mi_f1 - isn't this practically the same as the above?

Does anyone know how to get this to work if both ranges of the intersection are indirectly reffered to?

Notes:
mi_field - contains the name of another range ("January")
mi_f1 - is itself a range
(january mi_f1) - is an intersection which works

Thanks,
M
Comment
Watch Question

Author

Commented:
If I try: =INDEX(January INDIRECT(Z47)),B61)
It also doesn't work..... but if I try =INDEX(January mi_f1),B61)  it works....

So the problem is using mi_f1 indirectly... is there any way I can solve this?
Does something like this work:

=INDEX((INDIRECT(mi_field)&" "&INDIRECT(Z47)),B61)

Oops parentheses in the wrong places. Try this instead

=INDEX((INDIRECT(mi_field&" "&Z47),B61)

Author

Commented:
Hi Patrick,

I've tried and it still doesn't work, I've even tried the below, and doesn't work:
=INDEX(
      INDIRECT(
            INDIRECT(mi_field)&" "&INDIRECT(Z51)
            )
      ,B61)

Author

Commented:
Also tried the following and doesn't work :(

=INDEX(
      INDIRECT(
      "("&INDIRECT(Z53)&" "&INDIRECT(Z54)&")"
      )
      ,B61)

Author

Commented:
in my last message z53 and z54 contain the names of the ranges

Author

Commented:
Here is a workaround that I've just found but idealy I'd like to make it work in just one line:
Steps:
1. z53=January  z54=mi_f1
2. z57=(Z53&" "&Z54)
3. =INDEX(INDIRECT(Z57),1,1)

Author

Commented:
oops, sorry this is also not correct since it returns different values than INDEX((January mi_f1),1)

Author

Commented:
Just one more thing that I've discovered:

If mi_f1 is a uniform range like A1:D35 then it works like this: =INDEX(INDIRECT(Z28) INDIRECT(Z29),1) where Z28=January and Z29=mi_f1

BUT, if mi_f1 is a range that is defined as: A1,B5,C2,D3,etc (so contains just some cells) then it doesn't work. even if there exists an intersection between January and mi_f1
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
You could do this as well:

=INDEX(INDIRECT(M2) (A16:C16,A18,A20),1,3,1)

That accesses cell C16 - it's area 1, row 1 and column 3

mircea_a - Thanks for the grade - Patrick

Author

Commented:
you're welcome Patrickab, now I understand how these areas and indirect function work much better.

Thanks,
Mircea
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.