• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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
0
avhfj
Asked:
avhfj
  • 2
1 Solution
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now