Dashboard and Pivot Table Multiple Reference Help Excel 2010

[bissmeyer]

I have a large database of about 30 columns by 30,000 rows, that contains data on multiple people. I have made a dashboard with a pivot chart and multiple slicers to help organize my data. My end result is that when I select a crew and date from the slicers, the dashboard will display all the people in the crew that day and all of their data from the database. I added a coumn and I used the following code

 =('I&E Dashboard'!$E$29=[@[ENTRY_DATE]])*('I&E Dashboard'!$I$29=[@Crew])

to get 1s and 0s which was my way of doing a multiple reference lookup. I need a multiple reference because I have data across multiple days. I referenced the selection from the slicers so that only 1s would appear in the database by the names that had a matching crew and date.

I then used the following code on my dashboard

 {=IFERROR(INDEX(Table2,SMALL(IF(Binary=$D$29,ROW(Binary)),ROW(1:1))-1,4),"")}

to obtain all of the lines of data that had a 1 next to them. I copied this formula down for around 25 rows to make sure I got all of the people from the database.

The problem I am having is that this has now slowed down my worksheet immensley and it will often got into a non responsive mode and shut down.

Would anyone know a faster more efficient way to pull the data I want from my large database to my dashboard?

Thank you in advance.

Database example:

BINARY TRUE/FALSE

name

date

crew

data

data

data

data

1

<TBODY>
</TBODY>

 

[bissmeyer]

So the solution I've come up with so far was make a table on my dashboard and set the cells equal to the columns I wanted from my pivot table so I had a version of my pivot table on my dashboard. I then set up a filter between the 1s and 0s so that only the 1s (the true statements or the ones that match my criteria) are shown. The one problem I have is that it doesn't auto update so I just recorded a macro of the filter refreshing and made a button that refreshes it when clicked on.