Selecting a dropdown option to display records
 

Simplistically, my problem is this:

I would like to be able to put a combo box on sheet 1 that will allow a user
to select a choice (the combo box should be populated with unique records
from sheet 2, column b). Once a choice has been selected, all matching
records from sheet 2 will be displayed on sheet 1.

If anyone can point me in the direction of a similar example I would be
grateful.
--
Thanks,
MarkN

Selecting a dropdown option to display records
 

Use data validation rather than a combobox, as this uses a cell on the
worksheet rather than an object off the worksheet. You can then use VLOOKUP
formulae to look into your table, using the value selected from the DV cell.
If using a range on another worksheet to populate the DV, name that range,
and use that name in DV, else you will get an error.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Simplistically, my problem is this:

I would like to be able to put a combo box on sheet 1 that will allow a
user
to select a choice (the combo box should be populated with unique records
from sheet 2, column b). Once a choice has been selected, all matching
records from sheet 2 will be displayed on sheet 1.

If anyone can point me in the direction of a similar example I would be
grateful.
--
Thanks,
MarkN

Selecting a dropdown option to display records
 

Thanks for the response Bob,
I need something that will work for a generic list where unique items in
column B populate the combo box. This is key, can I do this with data
validation given that the list on sheet 2 is a dumped list where the values
change 3 or 4 times a day?
--
Thanks,
MarkN


"Bob Phillips" wrote:

Use data validation rather than a combobox, as this uses a cell on the
worksheet rather than an object off the worksheet. You can then use VLOOKUP
formulae to look into your table, using the value selected from the DV cell.
If using a range on another worksheet to populate the DV, name that range,
and use that name in DV, else you will get an error.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Simplistically, my problem is this:

I would like to be able to put a combo box on sheet 1 that will allow a
user
to select a choice (the combo box should be populated with unique records
from sheet 2, column b). Once a choice has been selected, all matching
records from sheet 2 will be displayed on sheet 1.

If anyone can point me in the direction of a similar example I would be
grateful.
--
Thanks,
MarkN

Selecting a dropdown option to display records
 

You can Mark, but it takes a little work.

Assuming your data is dumped into column A, in column B do this
B1: = A1
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

copy B2 down as far as you will ever need. It is an array formula, it should
be committed with Ctrl-Shift-Enter, not just Enter.

Create a dynamic name for column B (InsertNameDefine...) of say MyList
with a RefersTo value of

=OFFSET($B$1,,,COUNTA($B:$B),1)

and use MyList in the DV.

I have created a little example you can look at
http://cjoint.com/?edlxqUTtnT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Thanks for the response Bob,
I need something that will work for a generic list where unique items in
column B populate the combo box. This is key, can I do this with data
validation given that the list on sheet 2 is a dumped list where the
values
change 3 or 4 times a day?
--
Thanks,
MarkN


"Bob Phillips" wrote:

Use data validation rather than a combobox, as this uses a cell on the
worksheet rather than an object off the worksheet. You can then use
VLOOKUP
formulae to look into your table, using the value selected from the DV
cell.
If using a range on another worksheet to populate the DV, name that
range,
and use that name in DV, else you will get an error.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Simplistically, my problem is this:

I would like to be able to put a combo box on sheet 1 that will allow
a
user
to select a choice (the combo box should be populated with unique
records
from sheet 2, column b). Once a choice has been selected, all matching
records from sheet 2 will be displayed on sheet 1.

If anyone can point me in the direction of a similar example I would
be
grateful.
--
Thanks,
MarkN

Selecting a dropdown option to display records
 

Thanks Bob,
I will get this working and then I will try to understand it!
--
Thanks,
MarkN


"Bob Phillips" wrote:

You can Mark, but it takes a little work.

Assuming your data is dumped into column A, in column B do this
B1: = A1
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

copy B2 down as far as you will ever need. It is an array formula, it should
be committed with Ctrl-Shift-Enter, not just Enter.

Create a dynamic name for column B (InsertNameDefine...) of say MyList
with a RefersTo value of

=OFFSET($B$1,,,COUNTA($B:$B),1)

and use MyList in the DV.

I have created a little example you can look at
http://cjoint.com/?edlxqUTtnT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Thanks for the response Bob,
I need something that will work for a generic list where unique items in
column B populate the combo box. This is key, can I do this with data
validation given that the list on sheet 2 is a dumped list where the
values
change 3 or 4 times a day?
--
Thanks,
MarkN


"Bob Phillips" wrote:

Use data validation rather than a combobox, as this uses a cell on the
worksheet rather than an object off the worksheet. You can then use
VLOOKUP
formulae to look into your table, using the value selected from the DV
cell.
If using a range on another worksheet to populate the DV, name that
range,
and use that name in DV, else you will get an error.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Simplistically, my problem is this:

I would like to be able to put a combo box on sheet 1 that will allow
a
user
to select a choice (the combo box should be populated with unique
records
from sheet 2, column b). Once a choice has been selected, all matching
records from sheet 2 will be displayed on sheet 1.

If anyone can point me in the direction of a similar example I would
be
grateful.
--
Thanks,
MarkN

Selecting a dropdown option to display records
 

Good luck! You know where we are if you need further help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Thanks Bob,
I will get this working and then I will try to understand it!
--
Thanks,
MarkN


"Bob Phillips" wrote:

You can Mark, but it takes a little work.

Assuming your data is dumped into column A, in column B do this
B1: = A1
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",

INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

copy B2 down as far as you will ever need. It is an array formula, it
should
be committed with Ctrl-Shift-Enter, not just Enter.

Create a dynamic name for column B (InsertNameDefine...) of say MyList
with a RefersTo value of

=OFFSET($B$1,,,COUNTA($B:$B),1)

and use MyList in the DV.

I have created a little example you can look at
http://cjoint.com/?edlxqUTtnT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Thanks for the response Bob,
I need something that will work for a generic list where unique items
in
column B populate the combo box. This is key, can I do this with data
validation given that the list on sheet 2 is a dumped list where the
values
change 3 or 4 times a day?
--
Thanks,
MarkN


"Bob Phillips" wrote:

Use data validation rather than a combobox, as this uses a cell on
the
worksheet rather than an object off the worksheet. You can then use
VLOOKUP
formulae to look into your table, using the value selected from the
DV
cell.
If using a range on another worksheet to populate the DV, name that
range,
and use that name in DV, else you will get an error.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MarkN" wrote in message
...
Simplistically, my problem is this:

I would like to be able to put a combo box on sheet 1 that will
allow
a
user
to select a choice (the combo box should be populated with unique
records
from sheet 2, column b). Once a choice has been selected, all
matching
records from sheet 2 will be displayed on sheet 1.

If anyone can point me in the direction of a similar example I
would
be
grateful.
--
Thanks,
MarkN