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.
How do I Display Filtered Data from a Seperate Worksheet
- Thread starter mtr08004
- Start date
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
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
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()/1000000Where 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