I am trying to generate a list of all expenses from each quarter. I have attached a sample workbook with sample made up data.
For various reasons, I cannot name the tables or make them actual tables. So I am working with ranges rather than tables.
In the quarter 1 "table," I have the type (whether it was income, expense, or taxes) in column B.
In column C is the expense category (website, business learning, marketing, etc.).
In column D is the name of the expense (the user enters what they spent the money on).
In column E is the amount of money earned or spent.
(Images made up for examples)
setup.png
On a report page, I want to list all the expenses for each quarter.
So in the example above, the list would look like this:
Example.png
So to get the names of the expense, I tried this array formula:
{=IFERROR(INDEX('Template'!B9:E84,SMALL(IF('Template'!B9:B84="Expense",ROW('Template'!B9:E84)-1),ROW(1:1)),3),"")}
However, it just returned 0 rather than returning "Omnibus book" ( the text found in the 3rd column--column D in array B9:E84--for the first instance where B9:B84="Expense").
And then I thought to get the next instance of expense, which in the example would be "updates to formatting," I would have the same formula as above, but the row would change from 1:1 to 2:2.
To then get the expense of the omnibus book, I thought I would have this:
{=IFERROR(INDEX('Template'!B9:E84,SMALL(IF('Template'!B9:B84="Expense",ROW('Template'!B9:E84)-1),ROW(1:1)),4),"")}
So now having it return what it is in column 4 (the E column) of the array.
Etc.
It isn't working, so I am sure I don't understand how to properly use this array formula. Any ideas where I went wrong? Or other ways to get this list?
Bookmarks