Create excel table based filter

Question

Create excel table based filter

Navigation

#1 by (1 votes)
#2 by (0 votes)

5

Hello,

I have a table in Excel of 3 columns:

Pessoa | ID | País
João   | 1  | Flórida
Maria  | 2  | Flórida
Ana    | 3  | Miami
Ana    | 4  | Califórnia
Pedro  | 5  | Califórnia
João   | 6  | Flórida

etc ...

I would like to build a table that would only contain people from 'Florida' so I could reference it on another worksheet.

I tried to use Data Segmentation, but it works only as a filter, and I can not do dynamic table references (it does not get [@Person, for example, but referenced in the cell]). I would then build a subtable that would contain only the people of Florida, so that when I update the data from that table, that subtable will also update itself (but be a table, not a 'filter').

Does anyone have any suggestions? I tried a Query, but the subtable gives an error when I update my original table.

Thank you!

excel

asked by anonymous 07.02.2017 / 02:29

2 answers

Start and tear down test bank flask Deserialization of Object JSON morMot

score 1 · Answer 1

Does not Copy and Paste solve the problem? If so, use the command to select only visible cells:

Select your entire table or the columns you want to consolidate into your new list. Filter by 'Florida' and follow the instruction below:

Aba Início > Localizar e Selecionar > Ir para... > Especial... > Apenas célilas visíveis

Then select OK, press Crtl + C and paste in the desired area.

score 0 · Answer 2

Using a PivotTable would not help you? So you can put formulas next to the PivotTable and do not have the reference problem or hidden lines when filtering. In addition, you can insert Slicers to make the filters.

If you need guidance on how to assemble the PivotTable of this form, let me know!