Slicers from different data sources II

david763

New Member
Joined
Apr 3, 2012
Messages
19
Hi, repost of previous query - crunch time to finish this.

I am trying to put together an Excel solution for a friend who works with special needs people. I will not receive anything but a warm feeling for this - all voluntary.

FYI, I am developing in Office 365, however the workbook will be used in Excel 2016.

I have two main data sets:
A name list
A results list

I have already built pivot tables based on the results list, and added slicers to filter graph data. Currently slicing year level, calendar year results, teacher/class results, student results and subject results. (I am happy to re-do these pivot tables if need be)

What I am trying to do is set up a slicer on the same chart based on the YELLOW fields in the attached name list.

(1) I am having some trouble linking two data sets. I have been slowly working through this but not there yet (any advice welcome!).

(2) What is really doing my head in is the fact the YELLOW field names should be data values to be able to use these in a slicer (as far as I know) along with the slicers previously mentioned. I want the field names to be available as slicer values/selections and filter the results data accordingly.

I can't get my head around how to get this to work... Any suggestions?

(PS I don't mind if I need to do advanced data filter extract as I am already using a command button to refresh data, so adding extract in the same macro would be OK. However I need minimal manual data manipulation by the end user, AND the solution must be available in the suite of slicers)

I have attached a couple of pics of the (incomplete) data sets. Couldn't work out how to attach the workbook itself.

Prefer not the use of add-on, however will do so if need be - can you please tell me the steps though?

Many thanks for any suggestions...
 

Attachments

  • name list.png
    name list.png
    54.3 KB · Views: 1
  • results list.png
    results list.png
    65.2 KB · Views: 1

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
You can use Power Pivot to make a relation between the two tables.
You can use Power Query to unpivot the namelist in a new table with ID - Category - Value
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top