Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Displaying Multiple Values without using Macros or ROWS function
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('D epartment!$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:C 15)<$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))),"")) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying Multiple Values without using Macros or ROWS function
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. -- Regards, OssieMac "ltzhao" wrote: 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('D epartment!$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:C 15)<$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))),"")) -- ltzhao |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying Multiple Values without using Macros or ROWS function
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 -- Biff Microsoft Excel MVP "ltzhao" wrote in message ... 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('D epartment!$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:C 15)<$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))),"")) -- ltzhao |
#4
|
|||
|
|||
Hello T. Valko,
Thanks for your help. Unfortunately, the software doesn't support ROW() function either. I've figured out another way to get around the 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying Multiple Values without using Macros or ROWS function
Oh well! Thanks for the feedback!
-- Biff Microsoft Excel MVP "ltzhao" wrote in message ... Hello T. Valko, Thanks for your help. Unfortunately, the software doesn't support ROW() function either. I've figured out another way to get around the 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. -- ltzhao |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to retrieve multiple values in multiple rows with one criteria | Excel Discussion (Misc queries) | |||
Looking up multiple items and suming the values on their rows | Excel Discussion (Misc queries) | |||
Count on multiple values with duplicate rows | Excel Worksheet Functions | |||
How to Count Rows with defined values in multiple columns | Excel Worksheet Functions | |||
counting rows with same values for multiple values | New Users to Excel |