I am working on creating an interactive dashboard that filters data through a drop down list on the dashboard then displays it alongside the Scatter plot chart.
Basically the user would select the Clinic and/or the Provider which would then display the filtered list and scatter plot connected to it.
I am using Excel 2010 so pivot tables dont work well with scatter plots. I have attached a sample sheet of what i had in mind.
If I understand correctly this array-entered formula in C12 filled down and across to F21 of Dashboard should do what you want.
Formula: copy to clipboard
Formula:
Please Login or Register to view this content.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
I then changed the source x / y ranges of the scatter chart to the last two columns of that same Data Display summary. Click in which ever drop down you like. The data in Data Display changes and so does the chart.
Does this help?
File is attached.
Attached Files Attached Files File Type: xlsx Filtering a table using a dynamic drop down.xlsx (24.3 KB, 0 views) Download
Last edited by FlameRetired; 01-01-2016 at 02:04 AM.
Dave
The array entered formula (CSE) definitely worked! I'm not so familiar with them so will have to read up and dissect what's going on with the different pieces. Just a couple questions. Is it possible to filter on Clinic_ID or Provider_ID exclusively... what i mean is pull up only the records for say clinic 11... or provider 48? When I select clinic 11 , clinic 6 entry shows up in the list. ..also when say I choose provider_id 48 .. it pulls in other provider IDs as well (clinic id 15 in the example below).
Screenshot_2.png
The other question was concerning this error when i got when changing the provider or clinic id.. not sure if there is supposed to be a macro?
error.png
last question ( i know im killing you :D) .. what does the number in cell H5 represent?
Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.
If you need to post an image post it in the *.jpg format.
Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Before I can respond to the other questions ... like Tony Valko indicates ... we'll need another (current) Excel file upload.Originally Posted by hambon45
RE cell H5: I had the same question and assumed you had the answer to that since it was already in place in the initial upload. I left that alone and worked
with it. I observed that whatever ID in A5 or B5 is most recently selected returns the row number of it's first instance and that of the other ID in the
DVlists. It returned that row number in H5. Not knowing what else you might be using it for I used it.
Edit How that file contains a macro call is mystery to me. I reworked the file and tested it. It doesn't make that macro call now.
Last edited by FlameRetired; 01-02-2016 at 09:16 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks