How do I insert values of a named range into cells?

Posted on 2011-10-11
Last Modified: 2012-05-12
Hi guys

I've got two named ranges: city and shop
Please have a look at my spreadsheet. What do I need to do in order to display all cities from D2 downwards and all shops from E1 horizontally using the names.

Question by:mscola
    LVL 80

    Expert Comment

    Array enter the following equations in the same number of cells as the source ranges:

    Array-enter means:
    1) Select cells D1:D15
    2) Click in the formula bar
    3) Type the formula =City
    4) Hold the Control and Shift keys down, then hit Enter
    Excel responds by adding curly braces { } surrounding your formula
    LVL 80

    Expert Comment

    Since the above technique is probably not doing what you are needing, could you please describe your problem in context?

    Author Comment

    Well, it's not a problem.
    I just wanted to know whether it's possible to display all values of a named range.
    Or do I need to use VBA?
    LVL 80

    Expert Comment

    You may find it preferable to copy down a regular formula like:
    =IF(COUNTA(City)>=ROWS(F$2:F2),INDEX(City,ROWS(F$2:F2)),"")             Change the reference to cell F2 to the location of your formula (or someplace on that row)

    The above formula will return the different values of named range City until the list is exhausted. Afterwards, it returns an empty string (looks like a blank).

    The longer formula in this comment is useful when you don't know how many elements are in the named range (such as when it is a dynamic named range).

    Author Comment

    Please excuse my ignorance. I tried putting the formula in cell D2 and I changed the code to


    I keep getting an error message at INDEX. What am I doing wrong?

    LVL 21

    Assisted Solution

    How bout this in D2 and paste into D3, etc:


    E2 would similarly be

    LVL 80

    Accepted Solution

    Here is your sample workbook with the second formula installed in D2

    Author Closing Comment

    Thanks a lot guys. Both of them worked!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    excel formula or VBA 7 35
    excell formula 4 17
    Match Values and Return to Cell 6 17
    MS Excel default colors in styles 2 12
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    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.

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now