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!