You are here:

Excel/transposing a dynamic range


Dear Bob,

if i have a named dynamic range called select_date of dates starting in a sheet called:insert_sheets starting at cell d4 and dates run across columns dynamically i.e. more dates can be added.

How do i get a list box (which expects a vertical list) to dynamically pick up all values in select_date dynamic range and display them in ascending date order such that a user can select a particular date and the read of the spreadsheet i can use with hlookups to pull back values into a defined area covering d5:d81 (or which ever column is associated with selected date)?

I have been playing with index to transform the list but am getting stuck with multiple dynamic ranges and the index function showing in the list box the currently available selection of dates in chronological order.

Any advice gratefully received?



You would need to have the transposed array on the worksheet somewhere, like array-entered =TRANSPOSE(D5:AD5), for example, then name that range and use IT in the listbox. In order to display them in ascending order you'd either need VBA to populate the listbox, or an involved set of formulas to do that (which I can supply if that'd be helpful, and if so, include your email so I can send a sample file).
I'm leading an online 2-evening/3-hr each class in VBA. Details here:

The class is held June 4 & 6 from 8-11PM est.
If you're interested in taking a live, online VBA class, check this out:

go to

on the left side, click the link "Virtual Classroom"

on the right side, click "Virtual Classroom Schedule"

click the MIDDLE Dropdown, scroll to Microsoft Excel Visual Basic for Applications

click "See your selection"

click Course Outline (or register, etc)

And/Or, see this announcement for in-person and more in-depth training (not only VBA):


There will be an Excel User Conference June 26-28, 2013 in Las Vegas.

There are 3 instructors: Bob Umlas(me)(an Excel MVP), Tom Urtis (an Excel MVP), and Szilvia Juhasz (an Excel trainer).  
Please email me at if you think you may be interested and

I will follow up with you when the details are known, including hotel, agenda, cost, etc.
Please use subject of "EUC Interest" in the email.

Hope to see you there!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Bob Umlas


I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at


Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Excellence, The Expert, Microsoft

BA in math, Hofstra University, 1965

Awards and Honors
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2017 All rights reserved.