Display range, if a condition is met

[prazad82]

Hello,


Is it possible to display a range when a condition is met. For eg: I have a list of Departments in a drop down. So, if I choose one of the departments from the list, the table below should display the employees who are in that department.


Thanks.

 

[Hui]

Use Conditional Formatting

Select the whole range assume A2:Z100

Lets say column D is Dept and C1 is the drop down

Add a CF

Use a Formula

=$D2=$C$1

Set a highlight format

Apply

 

[NARAYANK991]

Hi ,


Do you have the complete matrix of employees in each department ?


How is your data organized ?


Asking a general question , without much data , will get you a general answer , without any information !


Narayan

 

[prazad82]

Thanks Hui. I think I have not explained properly.


Narayan, yes I have the complete matrix (named ranges) in a separate sheet.


Then I have a new sheet with the "department" drop down.


Below that, I have a blank table ( this is where I need to get the data of the corresponding dept)


For eg: If I choose "HR", the table should show the complete list of people working in that dept.


Prazad

 

[NARAYANK991]

Hi Prazad ,


I think more information is required ; if you have multiple departments , say "HR" , "Sales" , "Planning" , "Marketing" ,... , and there are multiple employees in each department , I assume you have named ranges for each of the departments e.g. the named range HR can refer to the range =Sheet2!$A$2:$A$15 , Sales can refer to =Sheet2!$B$2:$B$9 ,...


Suppose the drop-down is in Sheet3 , in the cell B2 ; suppose your table starts from B10 and extends downwards ; then in B10 , you can have the following formula :


=IFERROR(INDEX(HR,ROW(A1),MATCH($B$2,Departments,0)),"")


entered as an array formula , using CTRL SHIFT ENTER.


HR is a named range , referring to : =Sheet2!$A$2:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A))


Departments is a named range , referring to : =Sheet2!$A$1:$D$1


Narayan

 

[Debraj]

Hi Prazad,


Can you please if below link is helpful for you!!

Otherwise you have to use Narayan's Formula for each Column..

http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/


Regards,

Deb

 

[NARAYANK991]

Hi Prazad ,


Please ignore the formula I have posted earlier ; it will not work.


Try this :


=IF(ISTEXT(OFFSET(Sheet2!$A$1,ROW(A1),MATCH($B$2,Departments,0)-1)),OFFSET(Sheet2!$A$1,ROW(A1),MATCH($B$2,Departments,0)-1),"")


Enter this as a normal formula in B10 , and copy down.


Narayan

 

[prazad82]

Hello Narayan,


Your Formula worked well! Thank you very much


Debraj, Thanks for your suggestion as well!