PDA

View Full Version : Dependant Combo Boxes


spannerj
07-24-2005, 03:19 AM
Hi there,

I was hoping someone could help me with this problem.

I have several combo boxes all using the same named range. When one of the combo boxes has an item selected I want that item to be unavailable in the other combo boxes.

I have looked at some older postings and cant find exactly what I need so I have attached my spreadsheet and hope someone will be helpfull enough to tell me what code I need to do this.

It's my first go with VB and I know some of what I have done isn't great but it works at the moment. I will improve with practice.

Cheers for any help.
Bob Phillips
07-24-2005, 05:47 AM
Take a look at http://www.xldynamic.com/source/xld.Dropdowns.html
spannerj
07-24-2005, 07:57 AM
Hi,

Thanks for the reply but I don't think this is quite what I want.

In the example you linked me to, selection of the 1st combo box then populates the second combo with a different list. What I want to do is populate it with the same list as the first box but without the displayed value.

Any ideas?
lucas
07-24-2005, 09:57 AM
Hi Spannerj,

If I understand you correctly, you wish to drop down 1 box and add a name, then in the next drop down you wish for the selection from the first drop down to not show and so on....if that is what your looking for this might help. You will have to look at both sheets closly to understand how it is working. Hope this helps. File attached
Bob Phillips
07-24-2005, 11:17 AM
Hi,

Thanks for the reply but I don't think this is quite what I want.

In the example you linked me to, selection of the 1st combo box then populates the second combo with a different list. What I want to do is populate it with the same list as the first box but without the displayed value.

Any ideas?

You could easily do that if you constructed the data correctly.
spannerj
07-25-2005, 10:39 AM
Thanks for the replies.

I am going to give your way a go Steve. I would never have been able to come up with a formula like you have if I stayed at my PC all year.

I was hoping there was a simple property in the combo box or a simple function to do it but I will try manipulating my lists and doing it that way.

Cheers
mark007
07-25-2005, 12:23 PM
There's a thread here on a similar theme that may or may not help:

http://www.vbaexpress.com/forum/showthread.php?t=257&highlight=combo

:)
lucas
07-25-2005, 04:53 PM
If you get in trouble, post back here. Someone will come along and help you. The file I posted will do what you want. Just look at it close. Check out the data validation that is commented in the sheets that is the key.
excelliot
07-26-2005, 01:19 AM
is it possible to link data validation in other sheet
Bob Phillips
07-26-2005, 03:34 AM
is it possible to link data validation in other sheet

Yes, name the range in the other sheet and use the name, or use INDIRECT.
excelliot
07-29-2005, 05:40 AM
Yes, name the range in the other sheet and use the name, or use INDIRECT.

I think u did not got me.

I wants to limit data entry in cell thru data>validation, List,
in the list i wants to give reference of range of data in another sheet. Is it posible to link data in other sheet.:dunno
Bob Phillips
07-29-2005, 05:51 AM
I think u did not got me.

I wants to limit data entry in cell thru data>validation, List,
in the list i wants to give reference of range of data in another sheet. Is it posible to link data in other sheet.:dunno

Yes, name the range in the other sheet and use the name, or use INDIRECT.

On the other sheet, use INsert>Name>Define... to give the range a name ans use that name on the DV List,

or

use INDIRECT in the DV List


=INDIRECT("Sheet2!A1:A10")
.
lucas
07-29-2005, 07:17 AM
XLD is saying that if you want to use data validation with your list on a different sheet then the list on the other sheet has to be in a named range and called in the validation by the name of the range. If your list is on the same sheet as your data validation drop down you don't have to use a range. Correct me if I'm wrong XLD
Bob Phillips
07-29-2005, 07:38 AM
XLD is saying that if you want to use data validation with your list on a different sheet then the list on the other sheet has to be in a named range and called in the validation by the name of the range. If your list is on the same sheet as your data validation drop down you don't have to use a range. Correct me if I'm wrong XLD

No correction, you are correct. But, you can also use INDIRECT in place of a named range, as I show.
lucas
07-29-2005, 08:38 AM
That does work XLD. Learned something new today. Thanks :thumb
Bob Phillips
07-29-2005, 08:48 AM
Learned something new today.

So, what's new. I do every day, that's what makes it good :)
excelliot
07-29-2005, 11:04 PM
No correction, you are correct. But, you can also use INDIRECT in place of a named range, as I show.

In which option i should use indirect in DV in "allow " ;
because list option is not supporting it.
Bob Phillips
07-30-2005, 06:27 AM
In which option i should use indirect in DV in "allow " ;
because list option is not supporting it.

There is no 'should'. Either works.
lucas
07-30-2005, 08:06 AM
In which option i should use indirect in DV in "allow " ;
because list option is not supporting it.

:hi: Hi Excelliot, try the workbook attached to this post. On sheet 1 is a highlighted cell. Check out the data validation on that cell. the Data for the validation list is on sheet 2. It uses List in the allow dropdown and indirect in the source window.
excelliot
08-02-2005, 12:09 AM
:hi: Hi Excelliot, try the workbook attached to this post. On sheet 1 is a highlighted cell. Check out the data validation on that cell. the Data for the validation list is on sheet 2. It uses List in the allow dropdown and indirect in the source window.

I was getiing error bcoz itried by this formulae in data>validation>allow>list


=INDIRECT(Sheet2!A1:A10)

instead of


=INDIRECT("Sheet2!A1:A10")

thanks freinds
:beerchug: