# Displaying Multiple Values without using Macros or ROWS function

#### ltzhao

##### New Member
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))),""))

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### jim may

##### Well-known Member
Why not use an auto-filter?

#### ltzhao

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP, Moderator
OK, we cannot use ROWS(), but can we use ROW()?.

D2 and I2 copied down.

Excel Workbook
ABCDEFGHI
1CompanyDepartmentName3CompanyDepartmentNames
2AXJohn1AXJohn
3AYJoe1Jane
4AXJane2Kerri
5BYBob2
6CZKate2
7AXKerri3
8
List

#### ltzhao

##### New Member
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

##### MrExcel MVP, Moderator
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
ABCDEFGHI
1CompanyDepartmentName3CompanyDepartmentNames
2AXJohn1AXJohn
3AYJoe1Jane
4AXJane2Kerri
5BYBob2
6CZKate2
7AXKerri3
8
List

Replies
15
Views
296
Replies
19
Views
330
Replies
0
Views
153
Replies
4
Views
260
Replies
0
Views
165

1,191,216
Messages
5,985,318
Members
439,956
Latest member
venky2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back