Dropdown lists with source list on seperate page?

Nathalie · May 23rd, 2002, 07:35 AM

I want every cell of a specific category to have a dropdown list for all the options of the customers. I am using data validation, but how can i implement this with a source list of customers on a different worksheet?

lenze · May 23rd, 2002, 07:43 AM

Instead of referring to cell ranges in Data Validation, use a named range. Select your source range and choose Insert>Name>Define. Enter a name such as List. Now in Data Validation field,enter =List. This will work across sheets

[ This Message was edited by: lenze on 2002-05-23 06:43 ]

Ian Mac · May 23rd, 2002, 07:44 AM

Quote:

On 2002-05-23 06:35, Nathalie wrote:
I want every cell of a specific category to have a dropdown list for all the options of the customers. I am using data validation, but how can i implement this with a source list of customers on a different worksheet?

You need to Name the Range that contains your list.

Highlight the area you need in the list

Method1

Click in the Name Box in the top right of the Excel (it should contain a cell reference)
Type a name which means something to you e.g. MyList (no spaces)
Hit return.

Method2

Goto Insert>Names>Define in the top box put a name that means something to you, (as above).
Hit OK.

Now in the Source box of Data Validation type =MyList

this should do it for you

Nathalie · May 23rd, 2002, 09:02 AM

Thanks Ian and lenze, very appreciated!
Nathalie

Quote:

On 2002-05-23 06:35, Nathalie wrote:
I want every cell of a specific category to have a dropdown list for all the options of the customers. I am using data validation, but how can i implement this with a source list of customers on a different worksheet?

Matt111 · Oct 12th, 2004, 06:13 AM

I thought i was good on Excel until I tried to get this validation across sheets using a name range. It doesn't work for some reason.
I named my range of cells "Month", then in the Validation area defined it as a List the source being "Month". But when i test it, the drop down just says "Month" i.e. the name of the name-range, not the values within it. Any ideas where I'm going wrong please? This is such an annoying thing to be stuck on!

ken2step · Oct 12th, 2004, 06:16 AM

Try using Custom in Validation instead of List and entering

=Month

Matt111 · Oct 12th, 2004, 06:27 AM

Strange, I tried custom list, then decided i needed the drop-down for the user to see, changed back to 'List' instead of 'Custom', and it worked. Probably just me having one of those mornings

eitherway, it works now, thanks for your help.

SNOWOLF · Jul 11th, 2006, 06:50 PM

Hello

EL newb here.

I am trying to set up a drop down list to use for tracking equipment. I have figured out how to get the dropdown set up with the different values that I need. ie: full - empty ,ect.

I would like each value(cell) to have a different background color for easy ID when doing a quik lookthrough,but I can't get the colors to transfer over. The source is on the same page.

Thx in advance.
Sno

Smitty · Jul 11th, 2006, 07:38 PM

Welcome to the Board!

If you only have 3 conditions, you can use Conditional Formatting. In that cell goto Format-->Conditional Formatting-->Cell Value Is-->Equal to-->YourValue-->Format as desited, then add a condition and repeat.

If you have more than three, then you'd need a VBA change event, which can be written pretty quickly.

Hope that helps,

Smitty

SNOWOLF · Jul 19th, 2006, 06:22 PM

Thx PennySaver

The conditional formating worked like a charm. If it is an easything I was wondering if you could explain how to do the vba change event.

I have 5 values that I am working with and would like the last 2 to change as well. If it is to indepth then I could live with only being able to change 3.

Thx Sno

Oct 12th, 2004, 06:13 AM	#5
Matt111 Board Regular Join Date: Jul 2004 Posts: 78	Re: Dropdown lists with source list on seperate page? I thought i was good on Excel until I tried to get this validation across sheets using a name range. It doesn't work for some reason. I named my range of cells "Month", then in the Validation area defined it as a List the source being "Month". But when i test it, the drop down just says "Month" i.e. the name of the name-range, not the values within it. Any ideas where I'm going wrong please? This is such an annoying thing to be stuck on!

Oct 12th, 2004, 06:16 AM	#6
ken2step Board Regular Join Date: Jan 2003 Posts: 615	Re: Dropdown lists with source list on seperate page? Try using Custom in Validation instead of List and entering =Month __________________ Ken WindowsXP64Pro & Excel2003-2007

Oct 12th, 2004, 06:27 AM	#7
Matt111 Board Regular Join Date: Jul 2004 Posts: 78	Re: Dropdown lists with source list on seperate page? Strange, I tried custom list, then decided i needed the drop-down for the user to see, changed back to 'List' instead of 'Custom', and it worked. Probably just me having one of those mornings eitherway, it works now, thanks for your help.

Jul 11th, 2006, 07:38 PM	#9
Smitty MrExcel MVP Moderator Join Date: May 2003 Location: Boulder Creek, CA Posts: 23,704	Welcome to the Board! If you only have 3 conditions, you can use Conditional Formatting. In that cell goto Format-->Conditional Formatting-->Cell Value Is-->Equal to-->YourValue-->Format as desited, then add a condition and repeat. If you have more than three, then you'd need a VBA change event, which can be written pretty quickly. Hope that helps, Smitty __________________ Smitty Every once in a while, there's a sudden gust of gravity... Microsoft MVP - Excel Mr Excel HTML Maker - Post a shot of your sheet

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 07:35 AM	#1
Nathalie New Member Join Date: May 2002 Posts: 48	I want every cell of a specific category to have a dropdown list for all the options of the customers. I am using data validation, but how can i implement this with a source list of customers on a different worksheet?

May 23rd, 2002, 07:43 AM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Instead of referring to cell ranges in Data Validation, use a named range. Select your source range and choose Insert>Name>Define. Enter a name such as List. Now in Data Validation field,enter =List. This will work across sheets [ This Message was edited by: lenze on 2002-05-23 06:43 ]

Jul 11th, 2006, 06:50 PM	#8
SNOWOLF New Member Join Date: Jul 2006 Posts: 2	Hello EL newb here. I am trying to set up a drop down list to use for tracking equipment. I have figured out how to get the dropdown set up with the different values that I need. ie: full - empty ,ect. I would like each value(cell) to have a different background color for easy ID when doing a quik lookthrough,but I can't get the colors to transfer over. The source is on the same page. Thx in advance. Sno

Jul 19th, 2006, 06:22 PM	#10
SNOWOLF New Member Join Date: Jul 2006 Posts: 2	Thx PennySaver The conditional formating worked like a charm. If it is an easything I was wondering if you could explain how to do the vba change event. I have 5 values that I am working with and would like the last 2 to change as well. If it is to indepth then I could live with only being able to change 3. Thx Sno