In one sheet I have a list of expenses with category. Categories are popup menu. I have thousands of lines of expenses, so I created another sheet to summarize annual expenses, but I have listed each category manually based on my popup menu list on the previous sheet.
Is there a way to automatically list and update all items from that column in the first sheet, so if I add or remove or rename category, it updates on the summary sheet?
the easiest (in my opinion) is select the range of cells with the pop-up menu, then open the cell formatter:
Click the "+" while all the cells with the pop-up menus are selected:
Edit the name if the item:
the easiest (in my opinion) is select the range of cells with the pop-up menu, then open the cell formatter:
Click the "+" while all the cells with the pop-up menus are selected:
Edit the name if the item:
Krarcmed wrote:
Is there a way to automatically list and update all items from that column in the first sheet, so if I add or remove or rename category, it updates on the summary sheet?
If I understand you correctly you are trying to update the "distinct" values in the second table based on what you have in a column in the first table, something like this, where you. want A B C D in the first column of the Summary table.
Not fully automatic, but there is a quick way to do that using Smart Categories without using extra columns and formulas.
Which gives you something like this:
Right click a cell with a disclosure triangle and choose Collapse Peer Groups:
Select the displayed peer group names and command-c to copy to the clipboard:
Click once in a cell in the 'Summary' table and choose Edit > Paste and Match Style:
Giving you this:
Then turn off categories in the 'Expenses' table.
Not fully automatic, but it only takes a few seconds.
SG
Thanks to both of you. SG's suggestion is basically what I need, but as he said - not fully automatic. I'll use that for sure if there's no fully automatic solution.
Here's the more visual explanation of what I need.
These two tables are in two separate sheets.
First table represents the list of all transactions pulled out from the bank statements and I have them over 4000 now, so it's a pretty big list already and growing.
Second table summarize this huge list by year (altough I use Smart Categories on the first list to make all kinds of analysis), by expense category, by the bank account and the currency.
So far I have to update manually categories on the second table whenever I change something in the first table. Bank accounts and currencies I won't change (maybe once in a years, but that shouldn't be automated), but categories are more frequent to change as the new expenses comes that doesn't fit in any of the previous categories.
Here is some approach almost fully automatic.
First start by setting up your Transactions table and your Annual Summary table:
Formulas in the Annual Summary table:
Then you can move the Annual Summary table to the top of this sheet (or to another one):
For adding an item to a Category in the Transaction table, just click in the last row of the Category, and hit Enter. A new row for that Category will be created (the category will be automatically filled in).
You might hide column C, as you will not need to display it anymore.
You might change the Category name (= the Group name) in the Transactions table and the change will be automatically updated in the Summary table.
Paul.
Late to the party, but here is a fully automatic solution using an index column in the transactions table.
G3=IF(COUNTIF(C$2:C3,C3)>1,"",IF(COUNTIF(C$2:C3,C3)>1,"",ROW(cell)−COUNTBLANK(G$2:G2)))
G2=ROW(cell)
Column G can be hidden
Summary table formula
A2=INDEX(Transactions::C,MATCH(ROW(cell),Transactions::G,0),column-index,area-index)
I would hide errors in the summary table with a filter than only shows actual categories.
quinn
Actually, on a second though, SG's suggestion is even better than the fully automatic, as I have some categories that I don't want to count in the annual summary, so I'll use this, it really only takes a second.
List all items from popup menu
