Auto-populate certain rows in excel [closed]

Question

Not sure if excel has the power to do what I'm asking but I would like to create a worksheet and then from that worksheet choose specific rows to populate another work sheet simply by clicking on the row I want.

So for example i have information on rows 2-30 on worksheet A, but on worksheet B I only want rows 2,3,15 and 30.

Is there a way I can use 1 cell in worksheet A to populate worksheet B?

I know this is asking a lot from excel but if I can get it to work it would save me a lot of time and more importantly cut down a lot on the risk of making a mistake.

Any help is appreciated!

Answer 1

You have several options. One option is to write a macro to achieve what you need. Another option is to use Excel's PivotTable functionality. In Worksheet A, define your list. For example:

Item     | Enabled
------------------
Apple    |  1
Google   |  0
Subaru   |  1
Volvo    |  1

Select any cell in the above table, then go to Insert ribbon and click on Table button. This will define a 'table' object named Table1 that can be easily referenced.

In Worksheet B, select an empty cell, then go to Insert ribbon and click on PivotTable. Specify Table1 as the data source. In the PivotTable Fields pane (which will appear on the right hand side when any cell in the PivotTable is selected), drag Item column into Rows section and drag Enabled column into Filters section. In the PivotTable, click on the down-arrow next to Enabled cell and select 1 as the filter value. The PivotTable will now only show rows that are enabled.

You can make Pivot Table look a little slimmer by removing total rows: goto PivotTable Tools \ Design ribbon and click on Grand Totals button. Select Off for rows and columns.

Now, whenever you want to update your list, you just need to change the values in the Enabled columns to 1 or 0 (or any other value), then right-click the PivotTable and select Refresh.

Here's a screenshot of what the result may look like.