• Status: Solved
• Priority: Medium
• Security: Public
• Views: 205

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

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
Massimo Scola
• 4
• 3
2 Solutions

Commented:
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
0

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

Author Commented:
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

Commented:
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 Commented:
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

Commented:
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

Commented:
Here is your sample workbook with the second formula installed in D2
City.xlsx
0

Author Commented:
Thanks a lot guys. Both of them worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.