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

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
Asked:
Massimo Scola
  • 4
  • 3
2 Solutions
 
byundtCommented:
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
 
byundtCommented:
Since the above technique is probably not doing what you are needing, could you please describe your problem in context?
City.xlsx
0
 
Massimo ScolaAuthor 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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
byundtCommented:
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
 
Massimo ScolaAuthor 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
 
mastooCommented:
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
 
byundtCommented:
Here is your sample workbook with the second formula installed in D2
City.xlsx
0
 
Massimo ScolaAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now