Excel: INDIRECT and intersection of named ranges

Posted on 2011-05-07
Last Modified: 2012-08-13
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

Question by:avhfj
    LVL 81

    Accepted Solution

    You have to split the names:

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

    See attached.

    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.

    Please check the attached example.

    LVL 81

    Expert Comment

    by: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.


    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    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.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now