Hi all,

Can anybody help me please?

I am trying to build a spreadsheet that has a list of customers on on
sheet (that I control) and some other data on another that a user ha
to fill out.

The reason I want this is that all my users are updating data wit
different spellings of customers (eg Sainsbury / Sainsbury's) an
obviously then does not give accurate reporting.

What I want the user to do is to update new lines with data, but onl
be allowed to select the customers from my sheet (like a drop down lis
or combo box). I obviously don't want a combo box on each cell (it ha
3000 lines of data), but does anyone have any ideas on how I coul
force them to only pick my controlled list???

This could be through protection / macro / vb???

Anything anyone could suggest would be a great help....

Many thanks
Mark
:) :

--
certain_deat
-----------------------------------------------------------------------
certain_death's Profile: http://www.excelforum.com/member.php...fo&userid=2456
View this thread: http://www.excelforum.com/showthread.php?threadid=55138

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B"
Const WS_STORES As String = "M1:M10"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then
If IsError(Application.Match(Target.Value, Range(WS_STORES), 0))
Then
MsgBox "Invalid selection, redo"
Target.Value = ""
End If
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"certain_death"
wrote in message
news:certain_death.29cd68_1150203653.7643@excelfor um-nospam.com...

Hi all,

Can anybody help me please?

I am trying to build a spreadsheet that has a list of customers on one
sheet (that I control) and some other data on another that a user has
to fill out.

The reason I want this is that all my users are updating data with
different spellings of customers (eg Sainsbury / Sainsbury's) and
obviously then does not give accurate reporting.

What I want the user to do is to update new lines with data, but only
be allowed to select the customers from my sheet (like a drop down list
or combo box). I obviously don't want a combo box on each cell (it has
3000 lines of data), but does anyone have any ideas on how I could
force them to only pick my controlled list???

This could be through protection / macro / vb???

Anything anyone could suggest would be a great help....

Many thanks
Mark
:) :)


--
certain_death
------------------------------------------------------------------------
certain_death's Profile:
http://www.excelforum.com/member.php...o&userid=24561
View this thread: http://www.excelforum.com/showthread...hreadid=551388

CD,

Try Debra Dalgleish's great site

http://www.contextures.com/tiptech.html

scroll down to the "D" section and find listings for "Data Validation
which in one form or another is what you are looking for. Debra giv
multiple examples and even downloads of workbooks showing th
techniques. You won't be disappointed.

HT

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=55138