Displaying Multiple Values without using Macros or ROWS function

L

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))),"")
 
O

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

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
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top