List Pull (v2000)

Topic

New Reply

I have a drop down that is tied to a list of main office names. Each main office has varying numbers of satellite offices and within each satellite is a different number of employees. I want the user to select the main office from the drop-down and then underneath it, have each satellite associated with this main office appear. I would also like each employee to appear under the satellite office name. Lookups won’t work so is there another way?
Prost!

Reply | Quote

Replies

It would help if you could attach a sample workbook showing the layout of the data you have. The names can be dummies.

Reply | Quote

Hi Hans,
The attached is a sample. Cell A3 is a simple drop-down. When selected, the main office info. populates using a lookup. Underneath it, I would like the satellite offices to appear and the associated employees under each office reporting to the main office. I set-up a simple layout illustrating what I want to show. (Column H contains sample data to populate the left side of the sheet).
Thanks!

Reply | Quote

I’d say that Access is much more suitable for this than Excel. I may have a look at it later.

Reply | Quote

Andrew

I was looking at this in between calls at work and I think I have come to the same conclusions as Hans. It appears that Access is possibly the best tool for this type of thing as it is relational and can pick up the one to many relationships better and show your excel facsimile as a form. my worth

Reply | Quote

I had a chance to play with this.

In Cell A3 instead of using Data validation (which does not always trigger events), I added a ComboBox from the control toolbox (not from Forms) to cover the cell A3.
I also changed the formula in B3:F3 to adapt for this modification
In B3 enter the formula:

=INDEX($H$14:$M$15,$A$3+1,COLUMN())

And copy B3 to C3:F3.

After adding the combobox, select properties and change:
LinkedCell:A3
ListFillRange:$H$14:$H$15

From the “ViewCode” you can see my routine (it is the sheet object in VB). The code will run when the combobox is changed. It will get the Office Code, clear the cells, then extract the satellite and employee info.

I have attached an example. Adapt as desired…
Steve

Reply | Quote

Does the thread starting at post 463,944 help answer your question?

Steve

Reply | Quote