Excel/transposing a dynamic range
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 http://www.iil.com
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 Bobumlas@hotmail.com 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!