Solved

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

Posted on 2011-10-11
Medium Priority
203 Views
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.

Thanks
Massimo
name.PNG
City.xlsx
0
Question by:Massimo Scola
• 4
• 3

LVL 81

Expert Comment

ID: 36949040
Array enter the following equations in the same number of cells as the source ranges:
=City
=Shop

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
0

LVL 81

Expert Comment

ID: 36949053
Since the above technique is probably not doing what you are needing, could you please describe your problem in context?
City.xlsx
0

Author Comment

ID: 36949148
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?
0

LVL 81

Expert Comment

ID: 36949244
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).
0

Author Comment

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

=IF(COUNTA(City)>=ROWS(D\$2:D2),INDEX(City,ROWS(D\$2:D2)),"")

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

0

LVL 21

Assisted Solution

mastoo earned 1000 total points
ID: 36949731
How bout this in D2 and paste into D3, etc:

=OFFSET(City,ROW(D2)-1,0,1,1)

E2 would similarly be

=OFFSET(Shop,COLUMN(E2)-COLUMN(\$E\$2),0,1,1)
0

LVL 81

Accepted Solution

byundt earned 1000 total points
ID: 36949847
Here is your sample workbook with the second formula installed in D2
City.xlsx
0

Author Closing Comment

ID: 36956512
Thanks a lot guys. Both of them worked!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month15 days, 1 hour left to enroll

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

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