• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I Display Filtered Data from a Seperate Worksheet

mtr08004

New Member
Does anyone know how I can display all of that data with a certain field assiocated to it from another worksheet? Example, I want to display all of the projects with an "active" flag, owned by a certain person in my "dash board" sheet. I have a main data dump from our corporate system with ALL of the numbers in one work sheet. I have a dashboard created, I pick the responsible person from a dropdown and want excel to search and display all of the projects that are assigned to that person. I am sorry is that is not descriptive enough, thanks in advance.
 
You have 2 Options

1. Setup your Data Dump as a Data Table and use the filter properties to sort and only display the data you want,

Add a Reference Column and use that as a lookup in your dashboard

This can be automated with VBA


2. Use formulas

On your Data Dump page add an extra column to the left of your data

Assuming your name is now in Column B add

Code:
=+IF(B2=DROPDOWN,1,0)+ROW()/1000000

Where DROPDOWN will be replaced by your Drop Down Reference

What this does is is make column 1 equal to either a 1 plus a small bit where it is equal to your name, or zero p[lus a small bit where it isn't


In a Second panel below your data dump area

You need to replicate all your fields from the above data where it is equal to the Name

Except in the first Column you want to rank each row based on the position of the Unique ID above


In a Third panel below your Second Panel area

In Column A, now put the numbers 1 to the number of rows of Data in the Data Dump

Column B.. will have a lookup to lookup the Column A value and retrieve the data from the second panel


If you have several thousand lines of data from your data dump, you will now have 3 lots of several thousands of lines of data.


Difficult to explain so I have put a small example here

http://rapidshare.com/files/389369602/Text_Sort.xls
 
Back
Top