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

You may want to use the list choice in Data Validation to restrict the data
entry in a cell to a specific choice list. For greatest convenience, give
your list of customer names a range name in the worksheet where the names
reside. Let's say you'll call it "CustomerName". Now click on a cell in the
worksheet where the customer is to enter the customer name and click
DataValidation. From the Settings tab, choose "List" from the "Allow" combo
box, then enter =CustomerName in the source box. You can modify the Error
Alert message if the user tries to enter a name that is not on the list.
Copy that cell down through the range of input cells you require.

"certain_death" wrote:


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=551386

Paul is way too coy.
Assigning the defined name (Insert=Name=Define) to the list of customer
names
is more than a convenience. It is required if you want to use data on
another sheet as the data source in the list option for data validation.

Also, if you need to process multiple cells, select all the cells at once
when apply the data validation and apply it all at once.

--
Regards,
Tom Ogilvy



"Paul Mathews" wrote:

You may want to use the list choice in Data Validation to restrict the data
entry in a cell to a specific choice list. For greatest convenience, give
your list of customer names a range name in the worksheet where the names
reside. Let's say you'll call it "CustomerName". Now click on a cell in the
worksheet where the customer is to enter the customer name and click
DataValidation. From the Settings tab, choose "List" from the "Allow" combo
box, then enter =CustomerName in the source box. You can modify the Error
Alert message if the user tries to enter a name that is not on the list.
Copy that cell down through the range of input cells you require.

"certain_death" wrote:


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=551386

Yep, you're right Tom, I forgot to mention that naming the source list is
actually necessary if the list is on a worksheet other than the data entry
worksheet. So now I'm coy and blushing:)

"Tom Ogilvy" wrote:

Paul is way too coy.
Assigning the defined name (Insert=Name=Define) to the list of customer
names
is more than a convenience. It is required if you want to use data on
another sheet as the data source in the list option for data validation.

Also, if you need to process multiple cells, select all the cells at once
when apply the data validation and apply it all at once.

--
Regards,
Tom Ogilvy



"Paul Mathews" wrote:

You may want to use the list choice in Data Validation to restrict the data
entry in a cell to a specific choice list. For greatest convenience, give
your list of customer names a range name in the worksheet where the names
reside. Let's say you'll call it "CustomerName". Now click on a cell in the
worksheet where the customer is to enter the customer name and click
DataValidation. From the Settings tab, choose "List" from the "Allow" combo
box, then enter =CustomerName in the source box. You can modify the Error
Alert message if the user tries to enter a name that is not on the list.
Copy that cell down through the range of input cells you require.

"certain_death" wrote:


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=551386

Guys that's brill...........

Exactly what I need...

Thanks so much.....

Take care
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