Creating drop-down validation menus with reduced ranges?

Attached an example of how to do it. Just look at the formulas in the validations and how the ranges are set in the range box. got this from http://www.contextures.com/xlDataVal13.html. tells you all about it there

Tweek

thanks for the help so far,

i've taken a look into it and its not quite what i'm looking for, and it a bit too complex for me to work out how to rewrite it into something more helpful. just to clarify, column A has a list of unique serial number for tools, columns B to D contain information about that tool, column D contains a Yes/No statment about whether the tool requires a PAT test in addition to it normal testing. what i want is to be able to make a drop down list of only the serial numbers that refer to tools which require the additional PAT test.

is it possible to produce such a drop-down menu?

would that prevent the 'removed' lines from appearing in the drop-down menu? or simply hide them from me when i'm looking at the list of tools?

also, i need the master list to remain intact so that other sheets (i.e. the ones that are not about PAT testing) can use it without issue. otherwise i might as well produce a series of 'master' lists that only contain the tools that are relevant to individual tests.

attach a sample workbook.
you'll get you problem resolved much faster.
modytrane

okay, here's a sample workbook, i don't know if its got too much information on it, but it is a severly reduced version of the original (and a lot more standard than the data that was handed to me).

the sheets are:

Master List - which contains all the raw data specific to the tools, including column F which tells you whether the tool is an electrical item that requires PAT testing.

Drop-Down Lists - this will be hidden from the user and is a place to store all validation lists that have a limited number of correct answers.

Standard Testing - this sheet contains example records for the last year. there is a Worksheet Change Event macro, that automatically populates a line with details of a tool once the serial number has been selected. all tools will require Standard Testing, so the drop-down validation menu in column A allows the user to choose freely from all possible serial numbers.

PAT Testing - this sheet should work nearly identically to the Standard Testing sheet, however, the drop-down validation menu should only allow users to select from tools that appear on the Master List as requiring PAT testing.

is there some way i can limit the drop-down menu so that it only includex tools that require PAT testing without having to write a second Master List?

hope that makes it a little clearer at least.

Take a look at the attached file.
The PAT Testing sheet has validation list assigned in column A.
This list represents only those tools that require PAT testing.

Your macro that assigns formulas in standard testing sheet isn't working right.
You need to fix it.

Hope this helps.
modytrane.

The formula in column H [Master List] is an array formula.
If you edit the formula, you have to sbmit it with ctrl+shft+enter.
You have hold down Control and Shift keys and hit Enter. Excel puts the { } brackets around the formula. You can't enter them manually, it doesn't work.


The formula you are creating with the Macro doesn't work on my machine, but it could be because I am using Excel 2003. If you are using a different version and it's working for you, let it run.

Another choice is to write the formulas in the worksheet [see attahced file] and you don't need the Macro. I have written the formulas in both Standard Testing and PAT Testing sheets. The formulas have been copied down to Row 100. You can drag them down further if you want. The result is same as your Macro. If you select an itme in Col. A , other cells on that row will be filled automatically.

Hope that clarifies it.
modytrane