Sorry for creating another post, I haven't been back in a while and my
original post is burried on page 5 or 6.

I'm not the best at getting this crap to work, so bear with me. Here is an
example file that I am working with. The button brings up a list of unique
countries. Each country can have multiple assets. When you select the
country, it returns that country to whatever cell is highlighted. I then
would like the a list of assets generated based upon what was return to that
cell.

The file is at http://www.nmberger.com/nodupes.xls

"N Harkawat" wrote:

My mistakle I inserted a wrong bracket
Now I have tested it and it works fine
This is the formula
=INDEX($Z$1:$Z$10,SMALL(IF($Y$1:$Y$10=$A$1,ROW(A$1 :A$10)),ROW(1:1)))


"Nick" wrote in message
...
It tells me I have entered too few arguments for this funtion.

"N Harkawat" wrote:

Say your Project name is in Y1:Y10 and assets associated with that
project
are in Z1:Z10
Now if the project # is in A1 and you want this A1 to be looked up then
use
this array formula(Ctrl+shift+enter)
On cell B1 type this formula
=index($Z$1:$Z$10,small(if($Y$1:$Y$10=$A$1,row(A$1 :a$10),row(1:1))))
and copy it down 10 rows.

It should give you all the assets within that project.

and copy it down
"Nick" wrote in message
...
I have a bunch of projects, each project has a bunch of assets under it.
I
am creating a spreadsheet so that when a user clicks on a project in a
list
box, it returns all of the assets/info related to the assets under that
project. I can get it to return one asset using the MATCH or INDEX
function,
but I am having trouble getting it to return all the assets. Any
ideas?

The formula that you have entered is not array entered (ctrl + shift + enter
instead of just plain enter)
Do this:-
Simply click on cell B1 where your formula resides
Press function key F2 on your keyboard
And press these 3 keys on your keyboard together Ctrl - Shift - Enter
You shall NOW see the formula with braces {} in front and at end after that
Copy and paste it down the column
In your example spreadsheet for Belgium I got the following assets
ACX2
ACX10
ACX15
ACX23
ACX27
ACX53
ACX64
ACX83


and a bunch of NUM# after that which can be easily eliminated using
conditional formatting


"Nick" wrote in message
...
Sorry for creating another post, I haven't been back in a while and my
original post is burried on page 5 or 6.

I'm not the best at getting this crap to work, so bear with me. Here is
an
example file that I am working with. The button brings up a list of
unique
countries. Each country can have multiple assets. When you select the
country, it returns that country to whatever cell is highlighted. I then
would like the a list of assets generated based upon what was return to
that
cell.

The file is at http://www.nmberger.com/nodupes.xls

"N Harkawat" wrote:

My mistakle I inserted a wrong bracket
Now I have tested it and it works fine
This is the formula
=INDEX($Z$1:$Z$10,SMALL(IF($Y$1:$Y$10=$A$1,ROW(A$1 :A$10)),ROW(1:1)))


"Nick" wrote in message
...
It tells me I have entered too few arguments for this funtion.

"N Harkawat" wrote:

Say your Project name is in Y1:Y10 and assets associated with that
project
are in Z1:Z10
Now if the project # is in A1 and you want this A1 to be looked up
then
use
this array formula(Ctrl+shift+enter)
On cell B1 type this formula
=index($Z$1:$Z$10,small(if($Y$1:$Y$10=$A$1,row(A$1 :a$10),row(1:1))))
and copy it down 10 rows.

It should give you all the assets within that project.

and copy it down
"Nick" wrote in message
...
I have a bunch of projects, each project has a bunch of assets under
it.
I
am creating a spreadsheet so that when a user clicks on a project in
a
list
box, it returns all of the assets/info related to the assets under
that
project. I can get it to return one asset using the MATCH or INDEX
function,
but I am having trouble getting it to return all the assets. Any
ideas?

Got the formula to work, can't figure out the conditional formatting. I've
tried Cell Value Is and Formula Is and can't seem to come up with anything.

"N Harkawat" wrote:

The formula that you have entered is not array entered (ctrl + shift + enter
instead of just plain enter)
Do this:-
Simply click on cell B1 where your formula resides
Press function key F2 on your keyboard
And press these 3 keys on your keyboard together Ctrl - Shift - Enter
You shall NOW see the formula with braces {} in front and at end after that
Copy and paste it down the column
In your example spreadsheet for Belgium I got the following assets
ACX2
ACX10
ACX15
ACX23
ACX27
ACX53
ACX64
ACX83


and a bunch of NUM# after that which can be easily eliminated using
conditional formatting


"Nick" wrote in message
...
Sorry for creating another post, I haven't been back in a while and my
original post is burried on page 5 or 6.

I'm not the best at getting this crap to work, so bear with me. Here is
an
example file that I am working with. The button brings up a list of
unique
countries. Each country can have multiple assets. When you select the
country, it returns that country to whatever cell is highlighted. I then
would like the a list of assets generated based upon what was return to
that
cell.

The file is at http://www.nmberger.com/nodupes.xls

"N Harkawat" wrote:

My mistakle I inserted a wrong bracket
Now I have tested it and it works fine
This is the formula
=INDEX($Z$1:$Z$10,SMALL(IF($Y$1:$Y$10=$A$1,ROW(A$1 :A$10)),ROW(1:1)))


"Nick" wrote in message
...
It tells me I have entered too few arguments for this funtion.

"N Harkawat" wrote:

Say your Project name is in Y1:Y10 and assets associated with that
project
are in Z1:Z10
Now if the project # is in A1 and you want this A1 to be looked up
then
use
this array formula(Ctrl+shift+enter)
On cell B1 type this formula
=index($Z$1:$Z$10,small(if($Y$1:$Y$10=$A$1,row(A$1 :a$10),row(1:1))))
and copy it down 10 rows.

It should give you all the assets within that project.

and copy it down
"Nick" wrote in message
...
I have a bunch of projects, each project has a bunch of assets under
it.
I
am creating a spreadsheet so that when a user clicks on a project in
a
list
box, it returns all of the assets/info related to the assets under
that
project. I can get it to return one asset using the MATCH or INDEX
function,
but I am having trouble getting it to return all the assets. Any
ideas?