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