PDA

View Full Version : [SOLVED] Validation Lists



markyc
07-24-2006, 04:02 AM
Hi all.
Please help, I am creating a spreadsheet that contains lists of policy numbers (column A) and against these policy numbers is held some coverage information (column B) regarding type of policy eg life, health, car.

Sometimes one policy number could appear on list more than once with different coverage information.

What i am trying to do is create a separate sheet with two boxes (cells)
Box 1 (A1) want to be able to select the policy number from a list of all policy numbers currently held in spreadsheet, which I can do by using data validation.
Box 2 (B1) I want the dropdown list to show types of coverage, but I want this dependent on what result is given in Box 1, I only want to show coverages for that policy number.

I know I can do this by just using the filter function on the main spreadsheet but I want to keep these two sheets seperate if possible.

However I am open to sugestions to another way of approaching this query.

Thanks

OBP
07-24-2006, 04:20 AM
Actually the best way is to import it in to Access and use that. :)
Does the display of "Coverage" have to be a drop down box?
Are you going to select them and do something more with the data?
Or can it just be a list on the worksheet, or a text box?

markyc
07-24-2006, 04:31 AM
Thanks for your reply, unfortunatly we don't have access at work.

Have tried to get it but powers that be think differently.

What I am going to do with infomation, policy number and coverage is move the relevant row from spreadsheet to another sheet, I have macro for this all ready.

I want to use this seperate sheet to select the policy number and then coverage so it makes it idiot proof

OBP
07-24-2006, 04:52 AM
Do you want the Policy Number duplicates removed from the Combo List?

markyc
07-24-2006, 05:10 AM
OBP

Thanks for your interest.

I don't need the duplicate removed what I have is a weekly report, policy numbers and coverages

And what I want to be able to do is as my team works though the weekly work is select the policy number, coverage press button and the macro moves the record from weekly report into completed report.

I have the macro for the move and could just have data validation lists choose everything from columns A and B but what to try and make it a bit smarter so users can only select a coverage which the policy number has.

Please see attached report which may help, as you can see policy number AZ958406 has two records so what I want is when AZ958406 is selected in dropdown list (A1), I only want the coverages that apply to AZ958406 to be shown for selection

OBP
07-24-2006, 05:17 AM
Can the sheet have "Named" ranges or does it arrive new every week?
Do you actualy need the second List box, or can your macro work with Array Variables?

markyc
07-24-2006, 05:26 AM
The cells can have named ranges I can set these up each week, or if they don't then i can leave as is

don't know anything about array variables so if you can give demo then please do

OBP
07-24-2006, 08:59 AM
makyc, sorry for the delay, the wife gave me a job to do.:mkay

Any way I have written you some code that uses column a for the first combo and column "M" for the second combo.
The VBA of the first como scans down column for the Combo selection (stored in cel f1 and when it finds it it stores the "cover" in an array.
When it has finished it puts the "cover" array in the M column starting at M1.
When you click the second combo it picks up the new values.
ps your worksheet already had named ranges.

markyc
07-25-2006, 02:22 AM
OBP

Again thanks for your help with this.

Wives always getting in the way, still love them however!!

Back to query, the code you gave me is excellant I am getting the results that I want however is it possible not to have the msgbox display, it is OK when I have only a few entries but I am think when I use this in my live system we usually have on average 200 lines of data and I don't want to have to press the OK button that many times, can this be automated

Rgds

Mark

Bob Phillips
07-25-2006, 03:28 AM
Just remove the MsgBox statements

markyc
07-25-2006, 03:56 AM
XLD
Thanks simple answer but effective.

Last query I have I have just entered some live data into the database but the combo box only lets me choose from the first seven entries, I have extended the name range 'Policy' to include all my policy number, please cna you advise how I can extend the combo box to be able to select one of all policy numbers

Thanks

Bob Phillips
07-25-2006, 04:58 AM
XLD
Thanks simple answer but effective.

Last query I have I have just entered some live data into the database but the combo box only lets me choose from the first seven entries, I have extended the name range 'Policy' to include all my policy number, please cna you advise how I can extend the combo box to be able to select one of all policy numbers

Thanks

Best to post what you have got as it is tricky working blind.

BTW, I noticed your solution repeats items in the combo, not a good practice.

OBP
07-25-2006, 05:55 AM
xld, that was why I asked the question about duplicates. :think:
markyc, I have added some code to the sheet activate event that will update your Range Name policy to whatever is in column "A". It is also on a macro that you can activate by Ctrl + z, so if you copy some data in to columns a & b you can reset the name.

markyc
07-25-2006, 07:17 AM
OBP:bow:

Thank you very much for your help.

It seems to be doing what I had envisaged

Will load into my test version to see if it works there.

Thanks again

Mark