"Mark Dullingham" wrote in
message ...
I am an Excel novice so please be patient!
My problem is this-
I have a drop down list set up in D2 in worksheet 1 which references a
list
of customers contained in column A in worksheet 2. When the selection is
made
in D1, sheet 1 it returns a contract number in E2 from column B in sheet
2
using VLOOKUP funtion (and so on down the sheet). As the enteries in
sheet 2
Col A are not a definitive list of possible customers, nor are the
contract
number in Col B a definitive list of job numbers that may be associated
with
a particular customer, the user can enter their own values into Sheet 1,
D2
and E2.
Pretty staight forward so far!
What I would like to happen would be 1 of 2 possibilities
1- If the user enters a customers name and job number that does not
appear
in sheet 2 col A and B, the entries will be added to the relavant columns
for
selection in the future.
2- If the user enters a customers name that does exist in Col A sheet 2,
but
ammends the job number (we use 1 type of job number for a contract and
another for one off specific jobs) that number is some how added to a
list
of
job numbers for that customer and when that customer is select in the
future,
further down the sheet, all associated numbers appear in another drop
down
list.

Changing the DV to update the list if a new entry is made is pretty trivial
with VBA event code, but the job number is much different. If you get the
job number via a VLOOKUP formula, if you allow the user to over-type it, the
formula is gone, so that is a no-no. What other way would you envisage the
user specifying/amending the job number?