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?

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?

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?

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?

Try this

=INDEX($Z$1:$Z$10,SMALL(IF($Y$1:$Y$10=$A$1,ROW(A$1 :A$10)),ROW(1:1)))

note that it will not work if you insert rows above the formula

--
Regards,

Peo Sjoblom


"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?

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?