list formula

[Ev1lZer0]

I am hoping to generate a list that would list out every instance of a certain category across multiple sheets.

I am running a spreadsheet that has 12 sheets (one for each month) and I am hoping to have a 13th page that would list out every expense on that spreadsheet sorted by category. Each instance would have to list a range as i would want to know the card used, date it happened, description, category and cost. all of which is manually input by me. I would also want this list to update as i input more information. either automatically or right-clicking ans selecting update would work. I'm open to pivot tables if that's that best solution and if they work across multiple sheets, im also open to a list formula or other options i haven't considered.

This may not be explained well, and I may need to answer some questions to get what I want. I have listed two examples, and these appear exactly the same on every page. I have also included a list of categories i use.

Credit Card
Credit Card	Date	Description	Category	Amount
Double Cash	1/1/2023	God Squad Church	Charitable Giving: Tithe	$102.50
Costco	1/2/2023	Twitch	Charitable Giving: Tithe	$10.82
Costco	1/5/2023	CC Payment - Costco		$827.18
Costco	1/7/2023	Walmart	Groceries	$16.61
Costco	1/7/2023	Walmart	Groceries	$60.60
Costco	1/7/2023	Costco	Groceries	$1.62
Double Cash	1/8/2023	Tickets: Cubs/Mariners	Savings: Travel / Vacations	$76.00
Costco	1/8/2023	Ay Caramba	Groceries	$9.47
Amazon	1/9/2023	Amazon - Laundry Bags	Groceries	$8.97
Amazon	1/11/2023	Amazon - Card Sleevs	Allowance: Johns	$16.19
Amazon	1/14/2023	Crystal Light	Groceries	$29.41
Double Cash	1/15/2023	CC Payment - Double		$1,565.15
Amazon	1/15/2023	CC Payment - Amazon		$186.74
Amazon	1/17/2023	Christmas Lights	Savings: Christmas Decor	$37.91
Amazon	1/18/2023	Food Saver Bags	Groceries	$21.17
Costco	1/12/2023	Costco	Groceries	$82.05
Costco	1/17/2023	Red Robbin	Dating & Entertainment	$41.52
Costco	1/19/2023	SQ Coffee	Charitable Giving: Blessing Money	$6.50
Costco	1/19/2023	Costco Gas	Vehicle: Fuel	$51.41
Costco	1/21/2023	Wrays	Groceries	$0.38
Costco	1/12/2023	Costco	Groceries	$10.77
Costco	1/25/2023	CC Payment - Costco		$432.73
Double Cash	1/15/2023	nteract Ministries	Charitable Giving: Offerings & Missions	$30.00
Double Cash	1/15/2023	HYTTOUBN	Charitable Giving: Blessing Money	$104.00
Double Cash	1/18/2023	LH Atlanta	Charitable Giving: Blessing Money	$54.14
Double Cash	1/19/2023	Etsy	Savings: Baby	$14.80
Double Cash	1/19/2023	Spectrum	Household: Utilities: Spectrum	$54.99
Double Cash	1/19/2023	Studio E	Hair Cut	$39.10
Double Cash	1/20/2023	NWMN	Charitable Giving: Tithe	$280.00
Double Cash	1/20/2023	Stone Church	Charitable Giving: Tithe	$102.20
Double Cash	1/20/2023	AG - Dues	Charitable Giving: Tithe	$25.00
Double Cash	1/21/2023	Jewlers Mutual	Expense: AFL ($180), Costco ($65), Amazon ($130), Ring ($65), & Ring Ins. ($50), Keeper ($38), iCloud ($36)	$47.00
Double Cash	1/21/2023	Steins - Door lock & Sprinkler Timer	New Home	$111.33
Double Cash	1/21/2023	Ace - Trager drip Trays	Groceries	$42.05
Amazon	1/25/2023	CC Payment - Amazon		$88.49
Double Cash	1/25/2023	Integrity Inspections	New Home	$525.00
Amazon	1/31/2023	Movie Rental	Dating & Entertainment	$6.49
Double Cash	1/27/2023	Isagenix	Groceries	$609.28
Costco	1/24/2023	DQ	Dating & Entertainment	$7.13
Costco	1/26/2023	Apple Music	Dating & Entertainment	$12.34
Costco	1/26/2023	Apple Music	Groceries	$12.34
Costco	1/28/2023	Wrays	Groceries	$13.02

Debit Card
Notes	Account	Date	Description	Category	Transfer Funds	Amount $$$
	Chase	1/13/2022	Expense: AFL ($180), Costco ($65), Amazon ($130), Ring ($65), & Ring Ins. ($50), Keeper ($38), iCloud ($36)		Chase Savings	$50.00
	Chase	1/13/2022	Expense: Car Insurance		Chase Savings	$85.00
	Chase	1/13/2022	Expense: Home Insurance		Chase Savings	$95.00
	Chase	1/13/2022	Expense: Household: Property Tax		Chase Savings	$310.00
	Chase	1/13/2022	Savings: Travel / Vacations		Chase Savings	$350.00
	Chase	1/13/2022	Savings: General		Chase Savings	$857.00
	Chase	1/13/2022	Baby		Chase Savings	$300.00
	Chase	1/13/2022	Investments		Chase Savings	$300.00
	Chase	1/3/2023	Erin's December Tithe			$335.14
	Chase	1/3/2023	Tickets MLB		Chase Savings	$29.05
	Chase	1/5/2023	Mortgae	Household: Mortgage		$1,436.81
	Chase	1/5/2023	CC Payment - Costco			$827.18
	Chase	1/5/2023	Cascade Gas	Household: Utilities: Cascade Natural Gas		$128.41
$1,547.04	Chase	1/10/2023	YC Payroll - Erin			$1,211.06
$2,646.17	Chase	1/10/2023	YC Payroll - John			$2,056.52
	Chase	1/13/2023	Tickets Mariner/Cubs		Chase Savings	$76.00
	Chase	1/13/2023	Savings: Christmas Decor		Chase Savings	$40.00
	Chase	1/15/2023	CC Payment - Double			$1,565.15
	Chase	1/15/2023	CC Payment - Amazon			$186.74
	Chase	1/17/2023	Wise	Charitable Giving: Offerings & Missions		$30.00
	Chase	1/18/2023	Apple Cash - Erins Décor	Allowance: Erins		$48.00
	Chase	1/18/2023	Pacific Power	Household: Utilities: Pacific Power		$85.81
	Chase	1/20/2023	Zelle	Phone		$44.28
	Chase	1/23/2023	Christmas Lights		Chase Savings	$37.91
	Chase	1/23/2023	Ring Ins & Baby Announcement		Chase Savings	$61.80
Check # 2019	Chase	1/23/2023	Escrow for 4406	New Home		$7,000.00
	Chase	1/23/2023	Escrow for 4406		Chase Savings	$7,000.00
	Chase	1/24/2023	Interest			$0.03
	Chase	1/25/2023	CC Payment - Costco			$432.73
	Chase	1/25/2023	CC Payment - Amazon			$88.49
1589.6	Chase	1/25/2023	YC Payroll - Erin			$1,251.37
2674.4	Chase	1/25/2023	YC Payroll - John			$2,077.38
	Chase	1/25/2023	YC Reimbursment - mialage			$30.63
	Chase	1/25/2023	Student Loans			$60.00
Check # 2020	Chase Savings	1/26/2023	Savings Transfer		Morgan	$1,000.00
	Chase Savings	1/26/2023		Savings: General		$0.83
	Chase	1/27/2023	Verizon	Phone		$290.29
	Morgan	1/31/2023	Interest			$0.64

Category
Allowance: Erins
Allowance: Johns
ATM Fees & Other Fees
Charitable Giving: Blessing Money
Charitable Giving: Offerings & Missions
Charitable Giving: Tithe
Clothing: Erins
Clothing: Johns
Dating & Entertainment
Expense: AFL ($180), Costco ($65), Amazon ($130), Ring ($65), & Ring Ins. ($50), Keeper ($38), iCloud ($36)
Expense: Car Insurance
Expense: Home Insurance
Expense: Household: Property Tax
Fitness
Gifts
Groceries
Hair Cut
Household: Décor
Household: Small Appliances
Household: Furnishings
Household: Home Improvement
Household: Mortgage
Household: Utilities: Cascade Natural Gas
Household: Utilities: Nob Hill Water
Household: Utilities: Pacific Power
Household: Utilities: Spectrum
Household: Utilities: Yakima Waste
Medical
New Home
Personal Enrichment
Phone
Savings: Christmas Decor
Savings: General
Savings: Travel / Vacations
Student Loans
Vehicle: Erins Car Maintenance
Vehicle: Fuel
Savings: Baby
Savings: Investments

 

[awoohaw]

are the columns identical in each sheet? Are there other merged cells beneath the 1st row (I'm assuming Debit Card/Credit Card is 1st Row)?
Yes and No are the answers, then you can use VSTACK to aggregate. then do pivot tables or UNIQUE and FILTERING as you want.

 

[Ev1lZer0]

Your assumptions are correct. The first row states debit card and is merged over the inputs you see above. And credit card and merged over the inputs above. There are no other merged cells below the first row. The columns are identical in each sheet.

Of the options you mentioned, what would be the easiest. To state again, I’m hoping to end up with one very long list that lists every row and sorts them by category then by date.

It’s only just occurred to me that since the columns are different for debit card and credit card, I may end up with two lists. And that is fine.

 

[awoohaw]

When I asked about identical columns, I meant for the entire workbook. The credit and debit worksheets look different:

mr excel questions 27.xlsm
	A	B	C	D	E
20	Debit Card
21	Notes	Account	Date	Description	Category
22	Credit Card
23	Credit Card	Date	Description	Category	Amount
Ev1lZer0_DebitCard

did you mean that debit sheets are all alike? and credit sheet are all alike?

 

[Ev1lZer0]

Ah. Yes, your questions makes more sense now. What you just said is correct.

As such, I am open to two tables: one for debit and the other for credit.

 

[awoohaw]

Okay.
1. All worksheets must be sorted first by type then whatever month sequence you want, but it is important to have the credit together and debit together with no other worksheet intervening.

2. create a credit worksheet, in cell A1 paste:

=VSTACK(Ev1lZer0_CreditCardJan!A2:E2,FILTER(VSTACK(Ev1lZer0_CreditCardJan:SheeEv1lZer0_CreditCardMar!A1:E20),
ISNUMBER(VSTACK(Ev1lZer0_CreditCardJan:SheeEv1lZer0_CreditCardMar!B1:B20)),""))

3. create a debit worksheet in cell A1 paste:

=VSTACK(
Ev1lZer0_DebitCardJan!A2:E2,
FILTER(VSTACK(Ev1lZer0_DebitCardJan:Ev1lZer0_DebitCardMar!A3:E30),
ISNUMBER(VSTACK(Ev1lZer0_DebitCardJan:Ev1lZer0_DebitCardMar!C3:C30)),""))

The two formulas are aggregating only 3 worksheets, you'll need to update this for the set up of your worksheets.

The best option for you however, is to do this is power query. I am not proficient in that to give a solution there. But it is relatively easy.
Do some YouTube searches for ExcelIsFun or Leila Gharani, or Minda Treacy and you'll find some great resources for Power Query and other excel features.

 

[Ev1lZer0]

Okay, I am not having luck with this. and part of the reason is because I don't want to resort my main data.

What if i did a unique formula for each category and just had the data expanded to capture the columns i needed it to. it would automatically only pull the category and be sorted by date.

 

[awoohaw]

The formula should not sort anything. I'm asking you to just keep the worksheets in an order that the worksheet part of the reference will work. Otherwise you have to click/type every worksheet to enter them into the VSTACK formula. Instead of :

Sheet1:Sheet12!B1:F30 you would have to type:

Sheet1!B1:F30,Sheet2!B1:F30,Sheet3!B1:F30,Sheet4!B1:F30,Sheet5!B1:F30,Sheet6!B1:F30,Sheet7!B1:F30,Sheet8!B1:F30,Sheet9!B1:F30,Sheet10!B1:F30,
Sheet11!B1:F30,Sheet12!B1:F30

and all this is doing is aggregating your data so you can do other reporting on it.

 

[Ev1lZer0]

OH! I love that, I have always done the long form. I like that I don't have to now!

I still am at a loss though. I don't understand how to modify the formula that you provided above.

if i told you the format for the names of my tabs are MMMYY (JAN23).

 

[awoohaw]

You'll have to change them to Credit202301(JAN23) and Debit202301(JAN23)... etc. but i'd suggest taking the parens out. it probably is no difference but i do not like using excel key symbols, and parens are definitely used by excel. Change them to something short and easy to recognize and easy to edit.
Not sure if you can use DEB or CRD and a number, as excel may think it is a cell reference. but you could do DEB or CRD with an underscore and a short date description
(If you use a space you will have to wrap the sheet name in apostrophes which is a pain).