How to filter rows from one table to another sheet based on category (dynamic/live updates)?

Question

pheonix2610 Author

Level 1

30 points

How to filter rows from one table to another sheet based on category (dynamic/live updates)?

Hi everyone,

I need help setting up a dynamic filter across sheets.

🧾 My setup:

I have a main table called “Expenses List” on one sheet, with these columns:

Date
Category
Sub-category
Item
Index
Price

In the “Category” column (Column B), I have values like "Transportation", "Hotel", "Food", etc.

I’ve created separate sheets named after each category (e.g., a sheet named “Transportation”, one named “Hotel”, etc.). I want each sheet to automatically display only the rows from “Expenses List” where the Category matches the sheet name.

I previously used this formula in each sheets to pull information from main table:

IFERROR(@INDEX(Expenses::Expenses List::'Sub-category',MATCH($A$1&""&ROW(reference)−1,Expenses::Expenses List::Index,)),"")

It used to work, but has stopped recently, and I can’t figure out why. Also, I’m not sure it’s the best approach for real-time, dynamic updates.

MacBook Pro 14″, macOS 15.5

Posted on Jul 13, 2025 1:04 PM

Reply

Answer 1

Badunit

Level 7

29,640 points

Jul 13, 2025 5:46 PM in response to pheonix2610

Assuming cell $A$1 has the name of a category (like Transportation) and the Expenses List::Index column has values that are the category followed by a number that increases each time (like Transportation1, Transportation2, House1, Transportation3, House2, and so on), your existing formula should work if you set the MATCH function to "find value". Right now it is missing that parameter and the default is "largest value". With "largest value" it should work fine until it runs out of results then it will repeat the final result. You do not need the @.

But now that we have some array functions, it is a lot easier to do this than what you are doing. The same result can be achieved with the following formula in a cell in the first row. It will spill into the rest of the rows:

=FILTER(Expenses::Expenses List::'Sub-category',Expenses::Expenses List::Category=$A$1)

Same basic formula for the other columns you want.

Even better, you can do it all with one formula in one cell:

The Expenses List table is on the Expenses sheet (and there is more than one Expenses List table, requiring the sheet name to be part of the reference in the formula given below).

The table name is one of the categories.

Formula in cell A2 =FILTER(HSTACK(Expenses::Expenses List::A,Expenses::Expenses List::C:F),Expenses::Expenses List::B=TEXTBEFORE(REFERENCE.NAME(,1),"::"))

This spills into all the other columns and the required number of rows.

HSTACK is there to make one array out of Expenses columns A and C-F (skipping column B).

TEXTBETWEEN with REFERENCE.NAME gets the name of the table, which will be the name of the category you want listed.

i do not use "header names as references" so my column references are the letters, not the words in the header row.

Reply

Answer 2

Badunit

Level 7

29,640 points

Jul 13, 2025 8:16 PM in response to Badunit

You might have noticed the "index" column doesn't have the correct values that would be needed for your formula. I was using "transp" instead of "transportation" as the category when I was testing your formula. It is not required for either of the array formulas and I forgot to change it before taking the screenshot.

I see that you said your sheets are the names of the categories. You can use TEXTBEFORE(REFERENCE.NAME(,2),"::") in the formula to get the category name instead of getting it from the table name like I did.

Reply