Variable Drop Down Dependent List with Multiple Criteria

[Spellbound]

Hi All

Despite doing some research on various sites, I have been unable to track a solution that matches this problem.

I have a attached a sample workbook, using just the basic info needed for this test.

In the Clients2 worksheet, I have drop down list of ID numbers in B1. Based upon the selection made in B1, I need a dependent drop down list in E3 based upon the results in Column D (List Worksheet) that match up with the selected ID (B1) in Column B of the List worksheet.

I would prefer not to use any helper columns, as there are many more columns and functions in use in both the original Pvt2 and List worksheets. Not sure but VBA may be more viable in this situation.

TIA ...David

[Simon Lloyd]

I don't think you'll be able to do that with forumla, you could do it with vba but you wouldn't be able to do it with a data validate cell as the range wouldn't be contiguous and you can't use an array property for data validation.

Put this in the Clients2 worksheet code module, it will create a named range each time you make a selection, you could then assign that named range to a combobox or something

Please Login or Register  to view this content.

[TMS]

This code will do what you want. It does, however, use two columns on the List sheet. These columns can be easily changed by changing one cell address.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I have updated your example. See attached


regards, TMS

[Haseeb Avarakkan]

If you can use a helper Sheet (you can hide this sheet), see the attached.

[Spellbound]

Hi guys

Thanks for the different solutions.

Going to try them out tomorrow and see which works best when incorporated within the main workbook.

David

[TMS]

And another variation ...

This still uses a "temporary work area" but it clears it completely after creating the DV list. The DV list, in this case, is a comma separated string rather than a named range.

It requires the UDF ConcatRange which is also listed below.

Regards, TMS

Please Login or Register  to view this content.

[Spellbound]

TMS

Was just about to give you an update when I saw your alternative solution. Will try that out now.

Thanks ...David

[TMS]

Note that you need to slightly change the change event.

Regards, TMS

[Spellbound]

Haseeb

Thanks for your solution but in this instance I prefer to go with a VBA solution. However, the idea of using a hidden worksheet may be useable for something else that I have in mind for this project.

Simon

I liked the idea of being able to produce a NAME range without helper columns but I could not get it to work with a dependent standard Dropdown Listbox and having looked at the Combobox scenario on Contextures, I felt this was not the way to go for me anyway, even if I could have got it to work!

TMS

Your last solution was great. I had been trying to find a way of adapting Simons code with yours but with my very limited knowledge of VBA, I was getting nowhere. This seems to do the trick but have a couple of small queries.

The main one is that the previously selected value left in E3 (Clients2) does not clear when you make new a selection by changing the ID in B1 (Clients2). So if the dependent list should have 5 numbers in it, it actually starts off as 6 numbers until you make a new selection in E3. I tried adding Me.calculate into the module but this did not work and likewise using <shift>F9 manually does not update the displayed range. Any ideas?

Is it possible to adapt the code so that the dependent list is sorted in ascending order, it just makes it easier for people to find the right number, especially if it is a long list.

The other thing that I find odd is that the dropdown list in E3 is always ranged left, even though the selection in E3 is ranged right, which is numerically correct. I have tried forcing the range to the right by formatting the list in Column D (List) to range right but this did not correct it either. Not sure if it matters but the numbers in this column use a custom format in the style 000 to force the zeros to show where applicable.

Hope I have not made this too complicated ...David

[Spellbound]

TMS

Just seen this:

Note that you need to slightly change the change event.

Not sure what event to change it to, I tried making it into a 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' but that created erroneous results.

Could you clarify this for me ...thanks

[TMS]

Just change CreateDropDown to CreateDropDown2 in the Worksheet_Change event.

Please Login or Register  to view this content.

Regards, TMS

[Spellbound]

I have already done that. I created a new workbook with the revised code to make sure it worked and that there would be no conflict with any old code. I thought you meant there was another change to make.

Regards ...David

[TMS]

The main one is that the previously selected value left in E3 (Clients2) does not clear when you make new a selection by changing the ID in B1 (Clients2). So if the dependent list should have 5 numbers in it, it actually starts off as 6 numbers until you make a new selection in E3. I tried adding Me.calculate into the module but this did not work and likewise using <shift>F9 manually does not update the displayed range. Any ideas?

Just need to clear E3 in the Change Event.

Is it possible to adapt the code so that the dependent list is sorted in ascending order, it just makes it easier for people to find the right number, especially if it is a long list.

I guess it should be possible to sort the range. I'll have a look.

The other thing that I find odd is that the dropdown list in E3 is always ranged left, even though the selection in E3 is ranged right, which is numerically correct. I have tried forcing the range to the right by formatting the list in Column D (List) to range right but this did not correct it either. Not sure if it matters but the numbers in this column use a custom format in the style 000 to force the zeros to show where applicable.

Ah, probably my fault as I didn't look closely at the numbers. I used TEXT to retain the leading zeroes in the DV List. I guess I can just remove the TEXT function. The entries will then right align but won't have leading zeroes. It depends what you want/need to do with the selected value. You could always use -- to coerce the value back to a number if you want to add them up.


Regards, TMS

[Spellbound]

Hi TMS

Just need to clear E3 in the Change Event.

I amended the Worksheet Change event as follows:

Please Login or Register  to view this content.

Which effectively clears the cell but it leaves it blank, even when the updated dependent list is added to the cell (E3). Not sure if I have explained that clearly but if for example the result is a single value, then it does not show in E3 until you click on the dropdown list and select the required number. Normally, the first value of the list appears automatically in the cell itself.

I guess it should be possible to sort the range. I'll have a look.

That would be great if you could resolve this within the code.

Ah, probably my fault as I didn't look closely at the numbers. I used TEXT to retain the leading zeroes in the DV List. I guess I can just remove the TEXT function. The entries will then right align but won't have leading zeroes. It depends what you want/need to do with the selected value. You could always use -- to coerce the value back to a number if you want to add them up.

Just to clarify the situation, the 3 digit number in column are the last 3 digits of a larger number, which is the combined number of C2 & D2 for example = 857231046. Therefore, the leading zeros are very important, even though they are not used in any form of addition etc, they are constantly linked to other worksheets.

Thanks for all your efforts ...David

[TMS]

I've coded most of this, just tidying up for my own benefit.

The cell E3 is cleared and the DV List is sorted.

Normally, the first value of the list appears automatically in the cell itself.

I don't believe this to be the case. If you set up DV, the cell will be empty until you select something from the list.

Will post updated code tomorrow.


Regards, TMS

[Spellbound]

That might be me, I think I was expecting the list to appear in the way that I described as the cell was technically blank/empty.

Leave it with you, as I will be away for most of the day tomorrow and probably not online until later in the day.

Regards ...David

[TMS]

OK. I think this does everything you asked for.

It creates a Dependent Data Validation List in Cell E3 ... which can be adjusted at the top of the code
It sorts the DDVL into ascending order
It populates the DDVL box with the first entry in the list

In the Client sheet, I have demonstrated formula and format combinations so you can see what you're getting ... and what you can do, if you wish

I've moved clearing the DDVL box, and setting it, into the body of the code so it's all in one place.

It does, however, use temporary storage on the List sheet which you can clear completely or in part ... just a matter of commenting lines in or out.
Where it puts the temporary storage can be adjusted at the top of the code, so it doesn't have to be too much of a pain. Bear in mind that it is likely to change the Used Range on the List Sheet which might have an effect if you print that sheet. I haven't tested it but it's something to be conscious of, especially if you retain the DV List and Named Range.

Regards, TMS

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Spellbound]

Hi TMS

Finished later than I thought and not having much luck with your code tonight.

The first time I downloaded your attachment; it would'nt open properly and I ended up with this error message:

Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\Documents and Settings\DavidT\My Documents\Provident\809568-DataValidationTest_3a.xls'
The following is a list of repairs:

Lost Visual Basic project.

Downloaded it again and did'nt get this problem, however when I changed the ID number in B1, the code stops on:

SortOn:=xlSortOnValues, _ with the error message "Compile Error - Variable not defined".

Next I created a copy of my original workbook and pasted your code into the worksheet and added the module but I still get the same error when I try to make a selection in B1. When I try to use the 'Run to Cursor' option in VBA it highlights the following rows of code:

Please Login or Register  to view this content.

I appreciate your efforts but unfortunately, I do not know how to fix this.

Regards ...David

[TMS]

I'll have a look tomorrow. I've been testing in 2007.

[TMS]

Yes, that was the problem. I'd been working with 2007 and, when I opened the sample data, it opened in 2007, supposedly in compatibility mode.

That was when I added the code to sort the DV List. Unfortunately, it used native 2007 sort code/functions which clearly don't work.

That's my excuse, anyway.

Replace:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

and the whole thing is:

Please Login or Register  to view this content.

Updated example attached. Hopefully, this will work for you.


Regards, TMS

[Spellbound]

Hi TMS

Just here for a short while then back out to work again.

I understand about the conflict between Excel versions, which is why I have avoided upgrading to a later version of Excel. I tried to use my main project with Excel 2010 and that corrupted it totally, so keep putting upgrading on the back burner for the time being.

I'm glad you posted the new attachment because when I tried to copy

and the whole thing is:

I discovered that it was still the old code, must be those pesky gremlins!

It seems to work fine apart from when you select an ID number that has only 1 matching number in the NUM Column (List); which results in NUM appearing in E3 instead of the actual number.

For eaxmple, if you select 1141850, the result in E3 = NUM but should be 015. The dropdown selection also shows only NUM as well. This action appears to be moving the headers in F1, G1 & I1 (List) down to row 2 for some reason. As I am not sure if columns F & G (List) are needed for the code or just there for testing purposes, I did'nt want to alter their structure or ranges.

Regards ...David

[TMS]

Sorry about the attached code, not sure how that happened ... I'll edit the post later so that it makes sense should it be of help to anyone else in the future.

I'll have a look at the single entry issue. I checked a two entry and I checked another with 28. Never occurred to me to test for one.

I'll get back to you. In the meantime, keep trying to break it!

Regards, TMS

[TMS]

Replacement Code. It was the sort not coping with the single entry. Does now ...

Hope this is everything you dreamed of ... and we can move on

Regards, TMS

Please Login or Register  to view this content.

[Spellbound]

Hi TMS

Thanks for the latest update.

Not around much on Thursdays or Fridays, so may not get a chance to check it out until the weekend.

Regards ...David

[Spellbound]

Hi TMS

Been experimenting with the code today but still having a few problems.

The sort appears to be putting the last value at the top of the list.

Please Login or Register  to view this content.

I tried amending the Offset line from (-1,0) to (0,0) as I think in your code the DV_Num range starts from Row 2 anyway. This worked correctly the first time that I made a selection in B1 but subsequent selections were not sorted correctly.

I also tried adapting the code ready to go into my master workbook, which involved changing the sheet names. However, I encountered a problem when I tried to change the cell adress from H1 to AA1, so that it would be clear of all the other used columns in the ISSUES worksheet.

Please Login or Register  to view this content.

Although the range was set up in the correct columns, the code failed to run. The sort was incorrect, plus it also failed to set up the Data Validation in the Client2 worksheet. I have attached my test workbook with this change, so you can see the problem. I tested it after just changing the sheet names and it worked correctly apart from the sort problem.

I also realised that when the code is incorporated in my master workbook, I am going to need to add either Me.Calculate to the Worksheet Change Event (Client2) or

Please Login or Register  to view this content.

Can you advise me as to which is the best way of doing this in conjunction with your code.

Regards ...David

PS: Sorry this is dragging on like this.

[TMS]

I am bemused. I tested it countless times ... obviously seeing what I expected. It seems to work OK for the single entry and for the 28 item list ... but not the original code. Very strange. I'll have another look and get back to you

TMS

[TMS]

In all honesty, I'm not sure why the Sort didn't run consistently. However, I've defined a couple more variables where I have determined the addresses for the Sort and it seems to be OK now. Everything is still based on the same starting point, I'm just disappointed that I could not work out how to adjust the ranges. But we can't spend forever on aesthetics ... it works.

The problem about moving the start address to column AA is that the way I determined the addresses, or at least the column, assumed I could extract the column using LEFT(..., 2) giving something like $I. Of course that doesn't work when you've got $AB$2. You'd have got away with column Y and we might never have known.

I've changed the sheet name to ISSUES, from List ... why didn't you have that in the example? No matter.

I've also amended the Change Event to monitor cells B1, E1 and E3. If B1 is changed, it will cause the secondary DDL to be created. When the name or the entry in the secondary DDL is changed it will Calculate the sheet.

And, finally, I've put some diagnostics in so that I can check what it has done; see below.

Hopefully, that covers all the issues.

Regards, TMS



Worksheet Change Event

Please Login or Register  to view this content.

Main Code

Please Login or Register  to view this content.

Diagnostic Output

Please Login or Register  to view this content.

[Spellbound]

Hi TMS

Just discovered your latest post and surprised that it was dated 21/01/12 as I have not received any email notification of your post.

Must admit, I could'nt even access the site yesterday and only just managed to get online now after their maintenance had finished.

I am hoping to implement the new code tomorrow and will give you a progress report at some point. This is an extremely busy week for me but would like to get this up and running this week if at all possible.

Just for the record, the Issues worksheet in the main workbook utilises columns A to U, which is why I thought it best to start from AA/AB as this would keep that data out of the way, especially if I needed to make use of more columns in that worksheet. Likewise, I tried to make the test workbook anonymous and I did not think it would be a problem for me to just change the worksheet name where applicable.

And, finally, I've put some diagnostics in so that I can check what it has done; see below.

That's a new one on me and looks quite useful but do I need to comment out that section of the code to stop it printing out a report each time.

Thanks again ...David

[TMS]

do I need to comment out that section of the code to stop it printing out a report

No, just don't put "debug" in cell E1. Even if you did, it's not actually "printing" anything, just listing it in the immediate window. I think that has a limit of 100 lines anyway.

Regards, TMS

[kvsrinivasamurthy]

Pl see the attached file.No helper cells.

[kvsrinivasamurthy]

If You want sorted list in E3 cell you can use this attached file.

[TMS]

@kvsrinivasamurthy: I like it, very neat.

I would suggest that you make it a Change event rather than a Selection Change event so it only fires when you change a value. And you might want to limit it to cell B3.

Regards, TMS

[Spellbound]

@kvsrinivasamurthy: I look forward to testing out your code later this week, especially as TMS is suitably impressed by it.

@TMS: Had a quick try with your code and trying to get time to implement it into the main workbook. May be a few days before I can get some proper feedback to you.

Thanks for all your efforts ...David

[kvsrinivasamurthy]

Many Thanks Mr TMShucks for your sugestions.
Thanks also to Mr Spellbound.
with regards
Kvsrinivasamurthy

[Marcol]

Why not just use native formulae with dynamic named ranges?

See the attached

[Spellbound]

Hi Marcol

Thanks for your efforts, I have downloaded your solution and I will try it out as soon as possible but tied up with work at the moment.

Will post details of the outcome as soon as possible.

David

[Spellbound]

@TMS

I decided to carry on testing your code & spent some time today working on it, which essentially works fine. However, the updating of other cells and the rest of the worksheet did not function as I think you expected. I created a cutdown version of my workbook to test it fully and made the following amendment to the mdropdown module to trigger the changes in the remainder of the sheet with the first selection in E3.

Please Login or Register  to view this content.

and likewise with the worksheet module:

Please Login or Register  to view this content.

Which appears to make everything update as it should but I am not sure if my changes are technically correct or in the correct place, as in the mDropDown module.

I wanted to make use of your 'debug' for testing, as I may need to adjust the ranges in the Issues sheet in the near future but I could not get it to work. I did notice that for some reason the DebugData macro does not appear in the macro list, even though the 3 'Test' macros are there.

There is one small problem, which I cannot resolve, in that although the number in E3 appears correctly ranged right; the numbers in the dropdown list itself are all ranged left for some reason. The source data for the Data Validation list in the Issues sheet is also correctly ranged right. Any ideas?

Thanks again ...David

[Shoieb.arshad]

Hi all,
i am a new user here. and i am also working on something similar to what you people have discussed. Sorry for being naive, but i have checked the last file "copy_datavalidation.xls" but when i open the VBA editor i didn't find the code which you people pasted above. am i missing something ? i am really very new to the writing macors.
thanks in advance.

[TMS]

@Shoieb.arshad: that particular solution didn't contain any code. It was demonstrating an approach that uses just standard formulae. If you want code examples, look further back in the thread.

However, note ...

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[Shoieb.arshad]

actually my task was exactly the same discussed in here, so that's why i didn't started any new thread.
i just want little explanation of the work discussed here in terms of implementation.

[TMS]

And you have had an explanation ... but you *are* hijacking another user's thread.

If you want to explore this further, open a new thread and link to this one with reference to specific issues and clarification required.

Regards, TMS

[Spellbound]

@TMS: Any thoughts on tidying up those bits & pieces in my last post (28/01/12 #37)?

@kvsrinivasamurthy: I have been testing out your code, which essentially works but I have encountered similar problems to the ones that I had in the early stages of this thread, which were mostly solved by TMS in his code.

I have amended the code as per the suggestion by TMS to a Worksheet Change event, which helps with the re-calculation plus I added "ActiveSheet.Calculate" at the end of the code.

The main problem is that when C2 changes, it does not clear E4 which is left over with the number from the previously selected dropdown list. This in turn results in a #N/A error, which is to be expected; however this could be disastrous if more than one customer has the same NUM number in column D (Issues), which could happen.

I tried to experiment by using some of the code from TMS solutions but with my very limited knowledge of VBA, I could not get it to work in any way.

The other cosmetic issues are that the numbers in the dropdown list should be formatted as 000 and if possible ranged right. When the appropriate number is selected from the list, it appears correctly aligned and ranged right in E4; it is just how they appear in the dropdown list. I tried looking up the properties of the VBA validation code "xlValidateList" but nothing seemed to cover these aspects.

I have attached a very cut down version of my project incorporating your code, which may make things clearer. Cell positions and references have been adjusted to suit where applicable.

I know this has turned into quite a long thread and I do appreciate everyones efforts.

David

[kvsrinivasamurthy]

Pl see attached file.I have changed code and formula in E8,E9 cell.Your views are welcome
with regards
kvsrinivasamurthy

[TMS]

@spellbound: this seems to be dragging on now and suffering from what we used to call "scope creep".

However, the updating of other cells and the rest of the worksheet did not function as I think you expected.

As far as I can see, it works exactly as I expected it to based on what was asked for. You change B1, it creates a drop down list and populates cell E3. You change cell E1 or E3, the worksheet re-calculates. I've put a test formula in place which is simply =E3*2. When I change B1, it creates the drop down list and populates E3 with the first entry and my test formula calculates. I don't need to calculate the sheet so I can only assume that Calculation is set to Manual in your workbook.

I created a cutdown version of my workbook to test it fully and made the following amendment to the mdropdown module to trigger the changes in the remainder of the sheet with the first selection in E3.

It would be helpful if you shared the cut down workbook so that we're all on the same page. You've had several versions of the workbook I have updated for you, plus those from at least two other contributors. The code may work differently depending on the structure and content of the workbook/worksheet(s) ... especially if *your* code switches off calculation, or you have it switched off to improve processing speed.

Which appears to make everything update as it should but I am not sure if my changes are technically correct or in the correct place, as in the mDropDown module.

Difficult to comment without seeing the full picture, workbook, content and code.

I wanted to make use of your 'debug' for testing, as I may need to adjust the ranges in the Issues sheet in the near future but I could not get it to work.

For the debug code to work, it needs the word "Debug" in cell E1 on the Issues sheet. I use this as an external "switch" mechanism.

Please Login or Register  to view this content.

I did notice that for some reason the DebugData macro does not appear in the macro list, even though the 3 'Test' macros are there.

It doesn't appear in the list of macros because it takes parameters.

Please Login or Register  to view this content.

There is one small problem, which I cannot resolve, in that although the number in E3 appears correctly ranged right; the numbers in the dropdown list itself are all ranged left for some reason. The source data for the Data Validation list in the Issues sheet is also correctly ranged right. Any ideas?

The numbers are Text in order to retain the leading zeroes. By default, text values are aligned left. I don't know of any way to cause the drop down box to right align text values ... though that's not to say it can't be done. The cell, E3, itself is formatted as right aligned, as are the cells in the DV list. So, as it stands, you have a choice, left aligned text values in the drop down box or numeric, right aligned values without leading zeroes in the drop down box.

The main problem is that when C2 changes, it does not clear E4 which is left over with the number from the previously selected dropdown list. This in turn results in a #N/A error, which is to be expected; however this could be disastrous if more than one customer has the same NUM number in column D (Issues), which could happen.

There was nothing in cell C2 in the examples I have been working with. Not sure how or why we would be expected to clear cell E4, which also had nothing in it. If you want to monitor cell C2, you need to adapt the Worksheet Change event to do that. It would only require a block of code to be copied and modified.

I tried to experiment by using some of the code from TMS solutions but with my very limited knowledge of VBA, I could not get it to work in any way.

You see, now I don't know what you are working with ...

Having looked at the workbook you have uploaded, the reason that cell E4 is not changing is because you are using code from kvsrinivasamurthy. That doesn't set the cell to the first value in the DV List. The Drop Down list is extracted in the loop but you don't update the value in the cell. Under the circumstances, I don't really know how appropriate that is ... it would mean that you get an appropriate value in the cell, but not necessarily the one required. Anyway, my code did that because that's what you asked for; kvsrinivasamurthy's doesn't. kvsrinivasamurthy's code also uses SelectionChange rather than Change.

The other cosmetic issues are that the numbers in the dropdown list should be formatted as 000 and if possible ranged right. When the appropriate number is selected from the list, it appears correctly aligned and ranged right in E4; it is just how they appear in the dropdown list. I tried looking up the properties of the VBA validation code "xlValidateList" but nothing seemed to cover these aspects.

Discussed above. But, as you say, it's cosmetic ... it looks fine to me. I think you're going to have to live with it.

I have attached a very cut down version of my project incorporating your code, which may make things clearer. Cell positions and references have been adjusted to suit where applicable.

Why would you "adjust cell positions and references to suit where applicable"? That just means that, when we give you code, you have to work out how to change it to work with the live workbook. That's not good use of anyone's time.

I know this has turned into quite a long thread and I do appreciate everyones efforts.

The original question was answered and resolved long ago.

If you want to test out different approaches, I think you would be better keeping them in isolation. You now have a sample workbook with all my code in it which is, essentially, redundant. kvsrinivasamurthy's code is being used to create the DV list.

My code was designed to be flexible and easy to adapt. It was/is fully commented so that you know what each part of the code does, even if you don't understand how it does it. Where there are alternative approaches, I have made that clear in the comments.

It seems that you have taken two approaches which both appear to do what you wanted, mixed them, mauled them and broken them.


When you ask for help of this nature, you need to:
.. ensure that the sample accurately reflects the live scenario
.. identify all your requirements from the outset
.. fix the requirement(s) at the outset so that you know what success looks like.
.. when you have a solution that works, run with it. You can always enhance and improve later.
.. if it's important to you, respond in a timely fashion
.. study and understand the code and what it does before you try to mix and match.


I enjoyed developing the approach to meet your needs but, as you may have gathered, I am a little frustrated right now. I suggest you take a couple of steps back to where you had a working solution and build on that.


I have provided an updated sample workbook that reflects the fact that the cells have moved down a row. This has required an adjustment to the cell references in the Worksheet Change event and also a slight modification to the code to cope with the input cell being B2 rather than B1.


Regards, TMS