Conditional Formatting and Filtering

Question

Hello!

I have a rather formula-heavy ongoing project that combines a VLOOKUP function with conditional formatting. 

On one page of my Excel file, I have a database with different types of training as column headers and job positions as row headers. This first chart indicates which jobs require which training sessions, and there are checkboxes that indicate 'TRUE' when checked off in line with the job and training requirement. 

On my second page, I have employee names and a drop-down list next to their names to select their job position. The rest of the column headers are the same as in my first database, and I have managed to use conditional formatting to highlight the cells corresponding to the types of training each employee requires. Once everything is set-up however, things get out of whack when I filter or alphabetize employee names or positions in either page. My VLOOKUP no longer highlights the values according to the job in the drop-down list. I'm not sure why, as the column number has not been changed (I know that it can't, otherwise the VLOOKUP wouldn't identify the right information). 

Here is an example of the database that I want to format ("DATABASE") :

	A	B	C	D	E
1	Employee Name	Job	Training1	Training2	Training3
2	John Smith	Sales Rep
3	Mike Baker	Accountant
4	Linda Clearwater	Sales Rep

Here is an example of the database with the job positions and relevant training ("JOBTRAINING") :

	A	B	C	D
1	Job	Training1	Training2	Training3
2	Sales Rep	TRUE		TRUE
3	Accountant		TRUE
4	Customer Service	TRUE	TRUE

This is the type of formula I  use in the DATABASE for the conditional formatting :

=VLOOKUP($B2;"JOBTRAINING";2;TRUE)

I then copy this formula in the next column, adjusting the column number in the formula as I go. 

Is there a better way to go about this? I would definitely need the capacity to filter by employee name or by position in each table, without changing the column order of course.

Answer 1

You should be able to do what you want in a Pivot Table, by joining the 2 detail tables together. I admit, I'm still a novice at the whole Power set. I've been able to join the 2 tables, but cannot take the final step to make the pivottable output you want.

! Power Pivot - Overview and Learninghttps://support.office.com/en-us/article/power-pivot-overview-and-learning-f9001958-7901-4caa-ad80-028a6d2432ed

Excel for Office 365, 2019, 2016, 2013

Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.

Power Pivot is available as an add-in for Excel, which you can enable by following a few simple steps. The underlying modeling technology found in Power Pivot is also found in the Power BI Designer, which is part of the Power BI service offered from Microsoft.

Start the Power Pivot add-in for Excelhttps://support.office.com/en-us/article/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8

Power Pivot is an add-in that you can use to perform powerful data analysis in Excel. The add-in is built into certain versions of Office, but by default, it's not enabled.

Import Data into Excel, and Create a Data Modelhttps://support.office.com/en-us/article/tutorial-import-data-into-excel-and-create-a-data-model-4b4e5ab4-60ee-465e-8195-09ebba060bf0

The sections in this tutorial are the following:

.  *    Import data from a database

.  *    Import data from a spreadsheet

.  *    Import data using copy and paste

.  *    Create a relationship between imported data

Extend Data Model relationships using Excel, Power Pivot, and DAXhttps://support.office.com/en-us/article/tutorial-extend-data-model-relationships-using-excel-power-pivot-and-dax-cf7197d3-1938-490e-93fb-20371e8dd67a

In this tutorial, you use Power Pivot to extend the Data Model, create hierarchies, and build calculated fields from existing data to create new relationships between tables.

.  *    Add a relationship using Diagram View in Power Pivot

.  *    Extend the Data Model using calculated columns

.  *    Create a hierarchy

.  *    Use hierarchies in PivotTables