Creating drop-down validation menus with reduced ranges?

**admiraldick** · 04-01-2009, 07:24 AM

Hi,

wasn't really sure how to phrase the title, but hopefully now that you're reading you can help me out.

i have a table of tools and their properties which i am using to provide validation lists for a number of other worksheets that record when those tools were last calibrated/tested and when they need to be calibrated/tested next.

many tools require more than one form of testing (electrical and/or calibration), but not all tools require both.

can i create some kind of drop-down menu the looks at the tool master list, but only gives the option of those that require the particular type of testing i'm recording on that particular list? how can i create a drop-down list that is more limited than the range i have selected for it to display?

**oldchippy** · 04-01-2009, 08:25 AM

Hi,

May be something like this?

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

**tweek333** · 04-01-2009, 09:38 AM

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

**admiraldick** · 04-01-2009, 11:19 AM

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?

**oldchippy** · 04-01-2009, 11:35 AM

Hi,

Why not just apply filtering to the column for PAT Testing and sort by Serial Number?

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

**admiraldick** · 04-01-2009, 11:53 AM

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.

**modytrane** · 04-01-2009, 01:18 PM

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

**admiraldick** · 04-02-2009, 05:31 AM

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.

**modytrane** · 04-02-2009, 09:32 AM

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.

**admiraldick** · 04-02-2009, 11:32 AM

brilliant, that looks like the sort of thing i need.

is it possible you could give me a brief run down of 'how' and 'why' it works, there are a couple of things about the formulas you've used in column H on Master List that i don't really understand like the use of '{' and if i look at the formula and then press enter it comes up with a different result.

again, thanks for your help, i hope i'm not been too much of a pain.

ps. not sure what's wrong with the macro for formulas. it seems to be working fine for me. if you are finding an error with it, i'd like to know so that i can try and find a solution so that it doesn't happen to other users once i pass it on.

**modytrane** · 04-02-2009, 04:31 PM

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