Displaying a portion of a table in a separate worksheet

Question

My workbook's first worksheet features a sizable table with numerous columns and rows (roughly 1000 rows and 20 columns).

I was wondering if it was possible to use the criteria from one of the columns in the main table to display a portion of this table in another worksheet within the workbook.

I'll use a real workbook example to help illustrate this. 

Each row on worksheet 1, which is the main table and is titled "General Employee Info," contains a distinct number that identifies an employee in Column A. (each employee has their own ID number).

Their office location (a city or municipality) is listed in Column B. The location of the office is indicated in Column C. Their annual pay is listed in Column D.

There are numerous additional columns that provide this kind of employment information, which is self-explanatory.

My question is, can I label Sheet 2 in the workbook "New York State" and have that sheet display all the same information as Sheet 1 (all the same columns) but only display the employees that work in the state of New York?

Answer 1

If you are using Excel 2013 or later, I highly suggest using the excel tables function as a starting point. Tables are awesome because they improve the user readability of your sheets and reduce the likelihood of mistakes in calculations like INDEX/MATCH.

Once the tables are in place with the main data, to subset I use the data modeling capability to link back to those tables.

These steps in Excel 2013 - version active around 2018. It's changed in Office 365 (see below)

1. create a sheet, or go where you want the table in your current sheet.
2. click the 'data' tab and select "connections" in the connections section.
3. from there select the drop-down on the add button and select Add to Data Model.. Connections Dialog
4. Click the tables tab. Click the table you want.
5. now close the dialog
6. on the data tab, and click Existing Connections in the "get external connections" of the data tab.
7. Click the Table tab
8. Select the table you want
9. Select the table radio button
10. Hit return (or new sheet or a different cell if that's what you want)
11. poof your table from another sheet is now reflected in the current worksheet.

You can now hide columns, add additional columns with new formulas to the right or left margins of the table, use data slicing to subset, etc.