Log In
Register
Home
New Member
April 19, 2018
Offline
COLUMN A is a list of names, ROW 1 is a list of dates, intersecting cells have a value such as X: Can I write a formula to search by row 1 for the coordinating information from column A if the X value is present? For example, If A2 contains “Billy Bob”, A3 contains “Phil”, A4 contains “Charles” and B1 contains “April 3”, and B2, B3, B4 (the intersecting coordinates) has “X”, I want to be able to search by the B1 value with the result of all corresponding A values. So search: April 3, Result: Billy Bob, Phil, Charles. It was suggested to use a pivot table which worked for what I wanted, however, if it's even possible - I looked at using slicers but it makes me use one for each date instead of being a whole list that I can search by date within. This is for tracking time off at work and this is the format that is used, I basically want to be able to easily search and see what people I have off on a specific date.
See Answer
July 16, 2010
Offline
Hi Wil,
I've moved this topic to General Excel Questions as it's regular PivotTables, not Power Pivot.
If you want to use a Slicer then you need to unpivot your data so that the date column headers are in their own column. You can use Power Query to do this.
In the attached file I've linked the query to your original data (now formatted in a Table). In the query I've unpivoted the data so the dates are in their own column and the Xs are too. This is the correct tabular format you should be working with for PivotTables and Slicers.
In the PivotTable sheet you can see the PivotTable and Slicer now do what you want.
Going forward you can update the 'Original Data' sheet and just click the 'Refresh All' button on the Data tab of the ribbon to update the PivotTable with new data.
You can learn Power Query here.
Mynda
Answers Post
1 Guest(s)
