asked on

Excel: INDIRECT and intersection of named ranges

Good afternoon, experts.

Please take a look at the attached spreadsheet. Can anyone tell me why cells C5:C8, D5:D8 and E5:E8 are not all the same as A5:A8?

If my entire approach is misguided, is there a way for me to reference a cell as an intersection when the name of a range is the contents of another cell?

I am using Office 2007 under XP SP3.

Thanks, Alwyn FJ

Intersection---Indirect-Problem.xls

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

Indirect "returns the reference specified by a text string", or it returns the the value of the cell referenced on a string. If you want to return the values on column B, just need to do something like =B5 on cell C5. INDIRECT() function can be used is you have a cell (say F1) where you have the text B5 and you make something like =INDIRECT(F1), this will get you the value of cell B5.

Please check the attached example.

jppinto
Intersection---Indirect-Problem.xls

zorvek (Kevin Jones)

If you want to keep the key in one column then you can split it on the fly:

=INDIRECT(LEFT($B5,FIND(" ",$B5)-1)) INDIRECT(MID($B5,FIND(" ",$B5)+1,999))

See attached.

Kevin
Intersection---Indirect-Problem-.xls

avhfj

ASKER

Excellent! Thank you Mr. Zorvek.
(and thank you for responding also Mr. Ipinto.)