Solved

# Excel: INDIRECT and intersection of named ranges

Posted on 2011-05-07
391 Views
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
Question by:avhfj

LVL 81

Accepted Solution

You have to split the names:

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

See attached.

Kevin
Intersection---Indirect-Problem.xls
0

LVL 33

Expert Comment

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.

jppinto
Intersection---Indirect-Problem.xls
0

LVL 81

Expert Comment

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

Author Closing Comment

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

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.