Make a choice in an "extensible" list

**ALOHA** · 01-14-2007, 04:23 AM

Hello Carim, once again,

A restriction in the use of validation makes a problem to me:the fact that the list has to be in the same sheet!

My situation: I have a WB with a dozen of sheets with the same structure where a dozen persons make entries of the same kind (bookkeeping).

If on can not bypass this restriction, that means that I have to have a list per person, instead of one list for all.

What do you think about that?

Regards

Aloha

**Carim** · 01-14-2007, 04:28 AM

Hi again,

With your validation list located in another worksheet, follow these steps,
after having selected the range
1. Insert Name Define
in order to give a name to your list ... say mylist
2. in the Data Validation menu
go to the Allow section, select List
go to the Source area, type
=mylist

Do not forget the sign =

That's it

**Carim** · 01-14-2007, 04:58 AM

Hi again,

As far as "extensible" is concerned, you can define your range with a "dynamic range" i.e a range which adjusts itself automatically ...

Insert Name Define
give your range a name
in the formula area type

=Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),CountA(Sheet1!$1:$1))

**ALOHA** · 01-14-2007, 01:51 PM

Hello Carim,

isn't it a problem that the introduction of the data is made in a dozen sheets?

Aloha

**MSP77079** · 01-14-2007, 02:24 PM

Hi Aloha (or, should I say Aloha, ALOHA?),

Sorry to enter this conversation in mid-stream. But, maybe I can help before Carim is back.

isn't it a problem that the introduction of the data is made in a dozen sheets?

Yes and No.
No, because all of the validation cells and combo boxes will refer to the same list.

Yes, because the Event code (i.e., Worksheet_BeforeDoubleClick and Worksheet_SelectionChange) would need to be on each sheet. UNLESS, instead of using these Events (which are related to individual worksheets), you used the equivalent Events, but at a workbook level:
Workbook_SheetBeforeDoubleClick
Workbook_SheetSelectionChange

How far along are you in getting this implemented?
Can you post a skeleton copy of your workbook to this forum, with some dummy data in it? (To post it here, you would need to first save it as a Zip file and upload the Zip file.)

**ALOHA** · 01-14-2007, 04:36 PM

Hi,

Thank you for your offer.
I post en example file. The text is in french, since I'm here in Luxemburg.

The sheet "Fournisseurs" has to contain the auto-extensible list.
In the sheets "Agent1" to "Agent3" the data is introduced, and for this purpose it's the column "Fournisseur" that is interesting.

If you have any question about it, I'm at your disposition.

Regards

Aloha