Displaying a portion of a table in a separate worksheet

0 votes

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?

Nov 3, 2022 in Others by Kithuzzz
• 38,000 points
661 views

1 answer to this question.

0 votes

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.

answered Nov 4, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to apply zoom animation for each element of a list in angular?

Hey @Sid, do check if this link ...READ MORE

answered Jul 30, 2019 in Others by Vardhan
• 13,150 points
1,585 views
0 votes
1 answer

How to retrieve the value of a text field in Flutter App?

Hi@akhtar, In your TextField, you can call one ...READ MORE

answered Sep 3, 2020 in Others by MD
• 95,460 points
1,813 views
0 votes
1 answer
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

We can use expressions as well in ...READ MORE

answered May 31, 2022 in Others by Sohail
• 3,040 points
675 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18, 2022 in Others by Edureka
• 13,690 points
428 views
0 votes
1 answer

How to Paste JPEG as a gif in excel?

Solution  Step 1 Navigate to the folder that contains ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
741 views
0 votes
1 answer

Can I use VBA in Excel 2010 Starter Edition?

No, unfortunately you can't use VBA in ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,600 points
512 views
0 votes
1 answer

How to create a drop-down list in Excel?

Making a list of the items you ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
732 views
0 votes
1 answer
0 votes
1 answer

Selecting a value of a table in Excel

Use this formula: =LET(step1,FILTER(A1:D6,A1:D1=B11), INDEX(D1:D6, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,600 points
379 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP