Displaying Multiple Values without using Macros or ROWS function

[ltzhao]

Hello Gurus out there,

I am working with a software itself doesn't support Macros and ROW
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 t
display the relavent names.

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


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

Sample Formula
IF($B$2="Company",IF(ROWS(C$15:C15)<$B$14,INDEX('Department!$F$3:$F$691,SMALL(IF('Department!$B$3:$B$691=$B$11,ROW(Department!$F$3:$F$691)-ROW('W
DELMIA HC ROSTE
2009'!$F$3)+1),ROWS(C$15:C15))),""),IF(ROWS(C$15:C15)<$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))),"")

 

[OssieMac]

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.

 

[T. Valko]

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

 

[ltzhao]

Hello T. Valko,

Thanks for your help. Unfortunately, the software doesn't suppor
ROW() function either. I've figured out another way to get around th
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

 

[T. Valko]

Oh well! Thanks for the feedback!