Displaying Multiple Values without using Macros or ROWS function
 

Hello Gurus out there,

I am working with a software itself doesn't support Macros and ROWS function.

My Goal:
is to display a set of data based on filtered information.

My Data base:
Company Department Name
A X John
A Y Joe
A X Jane
B Y Bob
C Z Kate
A X Kerri

Based on user selection of Company and Department, I want to be able to display the relavent names.

If user chose Company A, and Department X, I want to be able to display
John
Jane
Kerri


I've used the ROWS, Index, Small combination that works perfectly (Please see sample below). However, since the this software doesn't support the ROWS function, and doesn't support Macros, I am stuck. Can anyone provide any alternatives to this? Your help is highly appreciated.

Sample Formula
IF($B$2="Company",IF(ROWS(C$15:C15)<$B$14,INDEX('D epartment!$F$3:$F$691,SMALL(IF('Department!$B$3:$B $691=$B$11,ROW(Department!$F$3:$F$691)-ROW('WW DELMIA HC ROSTER 2009'!$F$3)+1),ROWS(C$15:C15))),""),IF(ROWS(C$15:C 15)<$B$14,INDEX('Department!$F$3:$F$691,SMALL(IF(' Department!$A$3:$A$691=$B$11,ROW('Department!$F$3: $F$691)-ROW('Department!$F$3)+1),ROWS(C$15:C15))),""))

Displaying Multiple Values without using Macros or ROWS function
 

This is a Microsoft Excel site so if you are not using that what software are
you using?

In Microsoft Excel AutoFilter will do exactly what you want.


--
Regards,

OssieMac


"ltzhao" wrote:


Hello Gurus out there,

I am working with a software itself doesn't support Macros and ROWS
function.

My Goal:
is to display a set of data based on filtered information.

My Data base:
Company Department Name
A X John
A Y Joe
A X Jane
B Y Bob
C Z Kate
A X Kerri

Based on user selection of Company and Department, I want to be able to
display the relavent names.

If user chose Company A, and Department X, I want to be able to
display
John
Jane
Kerri


I've used the ROWS, Index, Small combination that works perfectly
(Please see sample below). However, since the this software doesn't
support the ROWS function, and doesn't support Macros, I am stuck. Can
anyone provide any alternatives to this? Your help is highly
appreciated.

Sample Formula
IF($B$2="Company",IF(ROWS(C$15:C15)<$B$14,INDEX('D epartment!$F$3:$F$691,SMALL(IF('Department!$B$3:$B $691=$B$11,ROW(Department!$F$3:$F$691)-ROW('WW
DELMIA HC ROSTER
2009'!$F$3)+1),ROWS(C$15:C15))),""),IF(ROWS(C$15:C 15)<$B$14,INDEX('Department!$F$3:$F$691,SMALL(IF(' Department!$A$3:$A$691=$B$11,ROW('Department!$F$3: $F$691)-ROW('Department!$F$3)+1),ROWS(C$15:C15))),""))




--
ltzhao

Displaying Multiple Values without using Macros or ROWS function
 

Your software is compatible with the ROW function but not the ROWS function?

If that's the case then you can replace each instance of:

ROWS(C$15:C15)

With:

ROW(C15)-ROW(C$15)+1

--
Biff
Microsoft Excel MVP


"ltzhao" wrote in message
...

Hello Gurus out there,

I am working with a software itself doesn't support Macros and ROWS
function.

My Goal:
is to display a set of data based on filtered information.

My Data base:
Company Department Name
A X John
A Y Joe
A X Jane
B Y Bob
C Z Kate
A X Kerri

Based on user selection of Company and Department, I want to be able to
display the relavent names.

If user chose Company A, and Department X, I want to be able to
display
John
Jane
Kerri


I've used the ROWS, Index, Small combination that works perfectly
(Please see sample below). However, since the this software doesn't
support the ROWS function, and doesn't support Macros, I am stuck. Can
anyone provide any alternatives to this? Your help is highly
appreciated.

Sample Formula
IF($B$2="Company",IF(ROWS(C$15:C15)<$B$14,INDEX('D epartment!$F$3:$F$691,SMALL(IF('Department!$B$3:$B $691=$B$11,ROW(Department!$F$3:$F$691)-ROW('WW
DELMIA HC ROSTER
2009'!$F$3)+1),ROWS(C$15:C15))),""),IF(ROWS(C$15:C 15)<$B$14,INDEX('Department!$F$3:$F$691,SMALL(IF(' Department!$A$3:$A$691=$B$11,ROW('Department!$F$3: $F$691)-ROW('Department!$F$3)+1),ROWS(C$15:C15))),""))




--
ltzhao

Hello T. Valko,

Thanks for your help. Unfortunately, the software doesn't support ROW() function either. I've figured out another way to get around the problem. I had to split the information into two sheets, using max(), match() and index() to get around it. It's not pretty, but it works.

Thanks again for your help.

Displaying Multiple Values without using Macros or ROWS function
 

Oh well! Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ltzhao" wrote in message
...

Hello T. Valko,

Thanks for your help. Unfortunately, the software doesn't support
ROW() function either. I've figured out another way to get around the
problem. I had to split the information into two sheets, using max(),
match() and index() to get around it. It's not pretty, but it works.

Thanks again for your help.




--
ltzhao