Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drawing across Data from different workbook using long list.
Hi folks, This one's a real doosie! :-(
Although some of me previous questions have been basic learning, this really has me stumped. I can do it, but my way will take a long...LONG...time! So.....basically I have two workbooks. Book1 is my user interface, and Book2 is just a data storage workbook with a single worksheet. In Book2 I have a column of names for which I have created a dynamic named range for Column A. I have specified for this range that it will allow up to 3000 rows of data (Rows 2 to 3001 in Column A). The columns B to AH have other data corresponding to each name in Cloumn A....ok...so... In Book1 I have created a list box which is linked to the dynamic named range in Book2. Thus, my list box will always display only whatever names are included in those 3000 rows of column A (Maybe 20names out of a max of 3000 initially), and none of the blank rows further down........are you still with me?....The list box in Book1 has the standard cell link to display the row number of the name that has been selected when the user clicks on one in the list, (2,3,4,5.....2999,3000,3001). Also in Book 1, beside and around the list box I have a variety of cells, each of which displays the data corresponding to the employee, taken from the other columns in Book2. (In Book2 Cell A2 will be a name, Cell B2 telephone number, Cell C2 age etc to AH). There is a Botton beside the list box which says "Display Employee Data". When the user selects a name from the list and clicks the button all the other data for that name from Book2 will be drawn over using the Macro. At present I think I have to write this buttons Macro to use SELECT CASE. But I'll have 3000 CASE value lines of code to manually write. Surely there has to be a better way than typing in 3000 lines of VBA code for this? .....can I use a few different SUB/END SUB sections, and denote the cell link number as X or something in the code to draw the data across, and in my button macro only denote what X is equal to, VBA automatically adjusting to the value of X ? Now for the doosie......I actually have 72 data storage workbooks like Book2, each with dymanic ranges from 1000 to 5000 row values, so I'll be writing until next year doing it with Select Case!! HELP.....PLEasseeeeee!!!!! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation and a Long List. An Easier Way? | Excel Discussion (Misc queries) | |||
Extracting data from a long list | Excel Worksheet Functions | |||
Extracting data from a long list | Excel Worksheet Functions | |||
Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
Q. Consolidating a long list, in single workbook. | Excel Programming |