Selective Search Dropdown

[nd4spd]

Hi I need some help as i cant seem to figure out whether this can be done through vba or formula or magic.. What i am trying to achieve is to have a suggestive search selection with a drop down and on bases of that similar selection for the codes drop down to select from. I have attached an example to illustrate what i am looking for. Thanks if anyone could help.

[Marcol]

Why do you need a drop-down in F2?

Surely if you select the branch and the product then the code should enter automatically.
In F2

Please Login or Register  to view this content.

Unfortunately INDIRECT() in data validation, and lookups, doesn't like dynamic named ranges, so you are stuck with static lists.

You could use for data validation in B2
(The names must be identical to that in the Drop-down in B2)

Please Login or Register  to view this content.

insead of

Please Login or Register  to view this content.

Note
You have "MANGO APPLE" twice in your "London" list, the formula will return the code for the first instance found.

[nd4spd]

Marcol

I need a drop down in F2 as some items have same description but with different code which the inputer knows which one to select. This is reason why you could see MANGO APPLE twice in description but if you look it has different code 1003 & 2003...

If suggestive text can not be done is there are way in F2 option to select from.. for example MANGO APPLE in Description & under Code selection from 1003 or 2003.

Thanks

[modytrane]

Take a look at the attached file.
Type in a word in "E3".
Based on Selection in "B2" and the word in "E3", the lists for description and code will update.
Is this what you want?
modytrane

[nd4spd]

wow.. guess u have nailed it to one input..this will take me a while to figure out !!! I try and see if i can reshuffle to make it a bit more presentable as the data needs to be inputed below where you have the display box Thanks.. ))

Question how will it work for next input line for example Report B4 Essex Description Mango ???

[Marcol]

Try this workbook

It uses the change event in Sheet "REPORT"

Please Login or Register  to view this content.

[nd4spd]

Thanks a ton Marcol !! slight help needed in live worksheet.. In Report the Description is in Column G & Code in Column H.. and in Data instead of 3 cities(Column C:J) there are 4 cities (Column D:N) Please could you guide with the changes as to how i could do drop down for Code in Column H in Report... Thank you !! ))

[Marcol]

Can you provide a sample of your actual workbook.
The VBa as it stands is a bit inflexible, it can be easily adjusted, but if we see your actual workbook layout, and headers, etc, it might be possible to make it a bit more adaptable.

[nd4spd]

Hi Marcol

Attached sample !!

Thanks

[Marcol]

Why do you have codes without descriptions? That's going to make this very difficult.

[nd4spd]

I have put in most of the data code in there with live description such rent, rates, water, sales misc etc.. if you look on the data you will be able to see that such description are being repeated several times in each of the 4 coloumns.. the blank ones you can put anything that you like... hope this is useful...

[nd4spd]

Hi Marcol Attached new sample spreadsheet with description ran out of choice so you may find countries fruits and currency lists.. Thanks

[Marcol]

Okay that's a bit more doable, but how practicable it will be we'll wait to see, your file is already very slow for some reason.

Look back later today.

[Marcol]

Okay try this workbook.

Not sure how your workbook got to 10mb, but it's now 180kb and a bit more manageable.

The first thing to do is organise your workbook
With Sheet "Data"

1/. Sort each Set of lists e.g. "A CODE" and "A DESC"
Sort on "A DESC" > A-Z
Then by "A CODE" > Smallest to Largest

2/. Insert a Column after these two columns, call this Column "A DESC UNIQUE"

Then in
In row 2 of this column (F2)

Please Login or Register  to view this content.

Drag/ Fill Down
If your maximum column depth exceeds row 2000 the change the formula to cover what can reasonably expected and a bit more.

Create two Named Ranges "A_DESC" and "A_DESC_UNIQUE" (Note the underscores)
"A_DESC" is dynamic, you can add to this as you wish, if you delete a description, resort the lists so that you have an unbroken list of descriptions.
Name:= "A_DESC"
Refers To:=

Please Login or Register  to view this content.

"A_DESC_UNIQUE" will be used as data validation, it reduces your drop-downs to a more manageable length, but can't be dynamic as it will be called indirectly later on
Name:= "A_DESC_UNIQUE"
Refers To:=

Please Login or Register  to view this content.

Unfortunately this list will end with a lot of blanks, if you see a blank drop-down when this is called just scroll up.

Repeat similarly for all other groups, and any more you might want to add.

3/. Add a dynamic name for your companies
Name:= "COMPANY"
Refers To:=

Please Login or Register  to view this content.

Add to this list as required, but keep the list continuous, no blanks.

These wont be A,B,C, etc in reallity, so if "A" becomes "Fred Bloggs Ltd", then the lookup names "A_DESC" and "A_DESC_UNIQUE" must be changed to "Fred_Bloggs_Ltd_DESC" and "Fred_Bloggs_Ltd_DESC_UNIQUE" (again note the underscores)
For this reason best keep the names to short unique aliases, and later use these to lookup further details.

4/. With Sheet "Report"
Data Validation in Column "B"
Allow:= List
Source :=

Please Login or Register  to view this content.

5/. This code in the worksheet module should take care of the rest

Please Login or Register  to view this content.

Give it a go and let me know if it suits your needs.

[Marcol]

Well hush ma mouth ...

"A_DESC_UNIQUE" can be dynamic, forgot I was using VBa to call the validation, not

Please Login or Register  to view this content.

Name:= A_DESC_UNIQUE
Refers to

Please Login or Register  to view this content.

That should stop initial Drop-downs appearing to be blank.

[nd4spd]

Thanks Marcol.. sorry for the late response my machine got the kiss of blue screen so couldnt log on.. I shall give it go in understanding it needs bit of tweeking i shall give u a shout. thanks a ton again