Filtering with formulas

Question

I am inputting my data into a table in TAB A. for example purposes lets say the data in TAB A is Name,Date,color,band name. In Tab B i have a table and I'm pulling the data from Tab A into columns A,B,C,D using formula =Table1[@Name], =Table1[@Date] ext.. so Tab B is auto populating the data from tab A. the issue i am having is with columns E-M in TAB B. I am manually inputting data into these columns. So if i try to filter by name, columns A,B,C,D all filter in respect to Name, but columns E-M dont filter correctly in respects to columns A,B,C,D. I am trying to make it so if i filter by name or any other category the whole row stays the same in respects to the column im filtering, not just A,B,C,D.

Ex... TAB B

Name	Date	Color	Band	Animal	shoe size	TV show	Movie
John	01-31	Blue	Kiss	dog	6.5	House	Avengers
Adam	05-25	Red	Sting	cat	7.5	Scrubs	Saw
Steve	05-12	Yellow	Blink 182	Horse	12	Dexter	Spiderman
If filtered by name will do something like this, remember the first 4 columns are pulling data using a formula from Tab A.
Adam	05-25	Red	Sting	cat	12	dexter	Saw
John	01-31	Blue	Kiss	Horse	6.5	House	Avengers
Steve	05-12	Yellow	Blink 182	Dog	7.5	Scrubs	Spiderman

Tables E-M seem to be randomly sorted. Is there any way i can make it so all columns filter correctly?

Answer 1

Hi Noe G,

Thanks for contacting us,

It looks like you want to ensure that when you filter data in Tab B, all the columns (including columns E-M) stay synchronized with respect to the row data. To achieve this, you can follow these steps:

1. **Combine the Data**: Instead of manually inputting data into columns E-M in Tab B, you should consider combining all the data into a single table in Tab A. This way, when you filter by Name or any other category, all columns will stay synchronized correctly.
2. **Use Excel Tables (ListObjects)**: Convert your data in Tab A into an Excel Table (ListObject). To do this, select the data range in Tab A, including the columns Name, Date, Color, Band, and the additional columns E-M. Then, go to the "Insert" tab and click on "Table." This will create a structured table with headers.
3. **Formulas for Columns E-M in Tab B**: In Tab B, where you are pulling data from Tab A, you can continue to use formulas for columns A-D as you are doing now. However, for columns E-M, use Excel functions like VLOOKUP, INDEX, MATCH, or XLOOKUP to retrieve the data from the structured table in Tab A. This will ensure that the data in columns E-M is always synchronized with the filtered data in columns A-D.

For example, in cell E2 in Tab B, you could use a formula like this to retrieve the corresponding Animal based on the filtered Name:

=VLOOKUP([@Name], 'TabA'! A:E, 5, FALSE)

Repeat this for columns F-M, adjusting the column index (the last argument in the formula) accordingly.

4. **Filtering**: Now, when you filter by Name or any other category in Tab B, all the columns (A-M) will filter correctly and stay synchronized because they are based on formulas referencing the structured table in Tab A.

By following these steps, you can ensure that your data in Tab B stays consistent and synchronized when you apply filters to any column, not just A-D.

I hope this helps!

Regards, Sola