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
avhfjAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
You have to split the names:

   =INDIRECT("Name1") INDIRECT("Name2")

See attached.

Kevin
Intersection---Indirect-Problem.xls
0
 
jppintoCommented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
avhfjAuthor Commented:
Excellent!  Thank you Mr. Zorvek.
(and thank you for responding also Mr. Ipinto.)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.