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 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('Department!$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: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))),""))

 

[jim may]

Why not use an auto-filter?

 

[ltzhao]

I am not using the auto filter because I have to create this table in another file that's being used by another program which doesn't support ROWS functions and does NOT support Macros. And it has limitations on filtered rows to 500 rows, which is only a fraction of the database I have.

I have tried 3 different ways to solve the problem so far (ROWS, Macros, and Filters within the software itself), none has worked. I am looking for another way around this.

 

[mikerickson]

That other app doesn't sound like Excel.

Where does Excel come in?

Is Excel the source of the data and giving it to the other app?
If so, use Excel's filters to filter the data and then export a .txt to the other app.

 

[ltzhao]

The other application is Xcelsius, which uses excel as a basis to do reporting dashboards.
It supports most excel functions, but not some.

Is there another way to get around this?

 

[Peter_SSs]

OK, we cannot use ROWS(), but can we use ROW()?.

D2 and I2 copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Company	Department	Name		3		Company	Department	Names
2	A	X	John	1			A	X	John
3	A	Y	Joe	1					Jane
4	A	X	Jane	2					Kerri
5	B	Y	Bob	2
6	C	Z	Kate	2
7	A	X	Kerri	3
8
List

 

[ltzhao]

Thank you Jim, Mike and Peter.

Your help is very much appreciated. Unfortunately, the software doesn't support ROW() function either. However, using Peter's suggestion, and some manual manipulation, I got it to work at least. It's not pretty, and it will require manual management in the future, but at least it got around the problem.

Thanks once again for all of your help. It's highly appreciated.

 

[Peter_SSs]

I've not used Xcelsius but Googling tells me that AND, COUNTA, IF, INDEX, MATCH and MAX are available. If so, try this with D2 and I2 copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Company	Department	Name		3		Company	Department	Names
2	A	X	John	1			A	X	John
3	A	Y	Joe	1					Jane
4	A	X	Jane	2					Kerri
5	B	Y	Bob	2
6	C	Z	Kate	2
7	A	X	Kerri	3
8
List