Need Help Summing Data

Hi d'

It appears you may have passed the limit of items on the quick filter list. If memory serves, we came across a similar 25 item limit earlier. The 400 rows is not signifigant, but the 26+ merchant names is.

On solution is to set up an auxiliary column to hold either "show" or "hide" depending on the content of a 'trigger cell'.

Here's a example:

The formula shown below the tables will return "show" if cell A2 of Table 2 is empty (as seen here), OR if the content of the cell on 'this row' of the Merchant column (B, in Table 1) matches the content of cell A2 of Table 2.

In use, the 'filter' column would be hidden, and the filter applied would match the settings shown below:

Filtered to show Merchant A

Filtered to show Merchant B:

Although you could put a pop-up menu in Table 2::A2, I think you would find that such a menu might also be affected by the same 25 item limit as appears to be the case for Quick Filters.

Regards,

Barry

In the example discussed above, and shown again below:

Table 1::E2: IF(OR(LEN(Table 2::A$2)<1,B2=Table 2::A$2),"show","hide")

The formula puts "show" or "hide"in its cell, depending on the content of cell B2 on Table 2.

If Table 2::B2 is empty, or it the cell in 'this row' of column B contains the same text as Table 2::B2, the formula returns "show".

For any other value in Table 2::B2, the formula returns "hide".

Table 1 is filtered on column E, with the filter set to show only rows containing "show" in this column.

In use, Column E is hidden. The formula needs to 'see' it, but the user does not.

Regards,

Barry

What is the question?

If the question is "Can this be done?", then I would say yes. Income tracking is the same as expense tracking. The main question (IMO) is whether income exceeds expenses.

The ideal equation for budgeting is:

income + expenses = 0

Whatever work you've done on expenses, duplicate the sheet (or table), then duplicate it and name it income. Then name categories. The create a summary table to takes income from the income table and expenses from the expenses table.

If you select the relevant cells in your CATEGORIES table and set the Data Format to Pop-Up Menu Numbers will populate a Pop-Up Menu for you in each cell. You can then copy paste a cell anywhere you need in your document and the Pop-Up Menu will stay intact.

SG

Have you tried a filter? Just go up next to the column Letter and in the dropdown next to it choose Quick Filter. Choose Walgreens and all the rows with Walgreens in that column will be displayed. Quick and efficient.

SG

"Will both columns in Summary by Merchant populate themselves as transactions are entered in the Transactions table?"

No.

You will need to add each new merchant to column A when you first record a transaction with that merchant.

You will need to fill the formula in column B down to the row(s) containing the names of newly added merchants.

Once those two steps are taken, the calculations will update automatically as you add new transaction records in the Transactions table.

Regards,

Barry

"After applying Quick Filter"

What column are you filtering on?

What value have you set for the filter?

From the result shown, I suspect the filter value does not appear in the column to which the filter is being applied.

For example:

If you chose column F (Merchant) and set the filter value to "Sobey's" (a Canadian grocery chain), all of the rows wouls disappear, as you have not recorded any transactions at a Sobey's store.

I am puzzled, though at the disappearance of the column labels. The fill colour implies that these are in a header row, which should not be affected by the filter, and should remain visible. Is this, perhaps a non-header row, to which you have applied the fill colour shown?

Regards,

Barry

It's hard to know what's going on in your setup. If by Quick Filter you mean picking a value under the contextual menu that appears beside the column letter then I have no explanation. Assuming you have already tested whether this happens in a new doc as well, and you have already tried quitting Numbers and restarting your Mac, then you could try uninstalling Numbers altogether and re-downloading it.

SG

On the Mac (as opposed to iOS in some circumstances) you won't lose data when you uninstall the app. What I do is go to Launchpad, hold down the <option> key until the icons wiggle, and click the x by the app I want to uninstall. Once you've uninstalled, you would then re-download Numbers from the Mac App Store (Apple menu > App Store...).

SG