dynamically populate dropdown lists

Hi guys and girls,

I've got a bit of a challenge :

I have a sheet, on which in column A3 down there are part numbers - these are filled in by the user and have the format of 7 digits, starting with a 1. (For example 1234567)
These partnumbers COULD be on one of five other sheets, in column A of those sheets (sheet names are "1", "2", "3", "4" and "5")

Basically I want, after the user inputs the part number in column A, a dropdown menu (data validation is fine) to appear in the same row in column E, with the following results :

a) If the part number is found on for example sheet "3" and "4" -->
Cell in column E shows "pick your sheet" and has options "3" and "4" as data validation dropdown

b) If the part number is only found on sheet "x" (for example "2") -->
Cell in column E shows the sheet name "2" in the cell - no other options available

c) If the part number is found on none of the sheets -->
Cell in column E shows "none" - no other options available.

The check of this can be triggered on worksheet_change or even worksheet_selection change.

Is this possible and if so, how should I go about it.

I much prefer using VBA over formulas.

Thanks for any help!

hi hi - anyone? :|

Try this:-
The way you have named you sheets can lead to some confusion so I have changed a few thing:-
Your Basic Sheet is Called:- Data
The Five other sheets are named :- Sht1,Sht2,Sht3,Sht4 and Sht5.
The validation cell Headers have changed slightly, you will see. NB:- You require to keep the "_" in the header string
Everything else remains the same.
Nb:- When you Insert a number in columns "A" of shet "Data" and the Validation list arrives in column "E" and you select a sheet name, then the code will select that sheet, although you don't explicitly state that requirement.

Please Login or Register  to view this content.

Regards Mick

Hi Mick,

haven't tried it yet, but from reading the code, it looks like it will work splendid - I'll keep you posted!