Create filter based excel table

Asked

Viewed 915 times

6

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 contain people only from 'Florida' so I could reference in another spreadsheet.

I tried using Data Segmentation, but it only works as a filter, and I can’t make dynamic table references (it’s not [@Person], for example, but referenced in the cell). I would then like to build a subtable that contains only the people of Florida, so that when I update the data in this table, this subtable also updates (but be a table, not a 'filter').

Anyone have any suggestions? I tried a Query, but the subtable gives error when updating my original table.

Thank you!

excel

  • "I tried a Query", are you referring to that "Microsoft Query" option that is in the Data menu? Because I did a test here and it worked, edit the question and ask which error appears.

2 answers

1

Copy and Paste doesn’t solve your problem? If so, use the command to select only visible cells:

Select your entire table or columns you want to consolidate 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, hit Crtl+C and paste to desired area.

passo a passo

0

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

Tabela Dinâmica

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

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.