Excel - Automatic data entry into a second sheet based on criteria?

March 27, 2019 4:54 AM

I track my outgoing expenses in Excel 2016, one workbook per year. Each workbook contains a sheet with all expenses including the date and category of expense, then one sheet for each month with expenses broken down by category. I would like to be able to enter an expense ONLY in the first sheet and have it automatically update the corresponding month's sheet in the column for that category. Is this possible?

In the Everything sheet, I have columns for (among others) Date, Category and Amount. In the monthly sheets, there is one column for each category. I would like to be able to enter into Everything, for example: 27.03.2019 under Date, Groceries under Category and 32.10 € under Amount and have 32.10 € automatically appear in the sheet March in column Groceries.

To date I have been manually entering each expense in both sheets, but it happens often enough that I forget to put something in both that automation seems like it could be useful. I am all right with the basics of Excel, but beyond that require some explanation - e.g. if your answer is just "use X formula", please specify what values I should enter where!

posted by jeudi to Computers & Internet (14 answers total)

You need a function that says this: if the date is (X) month (where X=1 for January, X=2 for February), etc., display the cells from sheet one in sheet two.

Something along these lines, I think (warning: untested): =IF((A15,B15,C15,D15)="MONTH("10")",!OctoberExpenses(A15,B15,C15,D15),"")

Month function, If cell=x, place the value here.
posted by nosila at 5:30 AM on March 27, 2019

Easy. In sheet March, under column Groceries, use a combination of Index, Match, and Count formula.

You need an 'Identifier' column in your Everything sheet to count the number of items that are unique. Like =B2&C2 , which makes an identifier out of unique values of Date and Column values.

You count the number of rows in Everything sheet, Category column, that is labelled Groceries.

=INDEX('Everything'!D$2:D$500, MATCH($A1,'Everything'!$A$2:$A$500,0)

where A is your identifier column and D is Amount.

It's not quite correct, what I have written here, but hopefully you get a sense of it. Alternatively you could probably pay someone $5 to do it. It's really that easy.

You could use a pivot table, but that will be a bit more messier.
posted by moiraine at 5:40 AM on March 27, 2019

jeudi, clarify one thing please - do your requirements include handling multiple entries for any particular Category in a single month in Everything? e.g. you bought $10 Groceries on 3/1 and $20 on 3/2 so you'll want two entries in the Groceries column in the March tab: row 2 should have $10 and row 3 $20?

If that's the case I'm not sure this can be done with just cell formulas. Or at least it might be a lot cleaner to do with a macro instead of cell formulas.

A pivot table would make it extremely easy to get the Sum of category entries by Month, but I don't know about getting individual (by instance) categorical expenses out of a pivot.
posted by achrise at 6:13 AM on March 27, 2019

If you need to see individual entries by month (rather than a monthly summary by category), it seems like you could just use autofiltering for the results you need?

If you add at least one unique identifier to each row on the data entry sheet, it's trivial to use a pivot table to report something like:

MARCH.....CAT1...CAT2
..A...........100......0
..B...........150......0
..C...........0.........35
APRIL
..D...........90......0
..E...........0........25
..F...........50.......0

You just add the unique ID as a second layer of rows in the designer. If you need to keep everything on separate sheets by month, you can set the month as a filter instead of a row.

&c.
posted by voiceofreason at 6:44 AM on March 27, 2019

Response by poster: jeudi, clarify one thing please - do your requirements include handling multiple entries for any particular Category in a single month in Everything? e.g. you bought $10 Groceries on 3/1 and $20 on 3/2 so you'll want two entries in the Groceries column in the March tab: row 2 should have $10 and row 3 $20?

Yes, that is correct.
posted by jeudi at 6:48 AM on March 27, 2019

It kind of depends what level of aggregation you want in the monthly sheets. On the assumption that a monthly sheet that shows you all the detail of the summary sheet but only rows for that month is not what you want (because that would not help me with greater understanding of where my money goes, your preferences may be different) here are some ways to aggregate the information in the monthly sheets.

If you wanted just month and category totals:

You could use a version of sum if where your sum range is the date column and then your criteria are first the month and then the category. Assuming the ranges cover all the rows in the detail sheet - and you can just make the formula so it covers all the rows in the sheet - that just update the category totals as you add lines in the detail sheet.

If you're ok with totals not updating automatically and don't like sumifs you could just pivot the full detail sheet once you're finished with the updates - categories in the columns, months in the rows, you can also filter for a specific month or date range at the top. Alternatively, if you'd rather see categories in the rows you can put the months in the columns. Whatever is more meaningful for your tracking.

You could probably also pivot and have the individual line items for a specific date in your monthly view. Ideally you'd have a column with the exact date, one with the month and one with the category. You can then pull your pivot as before (columns are category, rows specific date, filter by month). That will show you category totals for specific dates.
posted by koahiatamadl at 7:03 AM on March 27, 2019

Yes, to reiterate, you can do it with a few simple formulas. You do not need a macro for this. As a matter of fact, I feel so strongly (I'm a sad Excel nerd) about this that I'm happy to do this for you, for ~$10 (or reasonable amount) donated to my favourite charity. MeMail me if you are interested.
posted by moiraine at 7:04 AM on March 27, 2019

Maybe the rules of Ask don't precisely allow this, but I'm very curious about the method to do this with just a few formulas. I believe that by design in Excel you cannot use a formula in one cell to affect the value in a different cell (prevents runaway circular references, e.g.). So to solve this with formulas the formulas must all be in the separate Month sheets. And because one requirement is that all individual expense instances maintain their uniqueness in the Month table that means that enough rows in the Month table must be populated with a formula to cover the maximum number of expected expenses in that category.

So ahead of time you have to think "How many times am I going to buy groceries in any given month", and then your Groceries column for that month would have to have that many rows of the formula. To make it simple (if this works) to implement I'd just populate the same number of rows for every expense and month.

Just for real-life numbers let's say there are 10 Categories and you want to plan for and a max of 20 expenses in any particular category, that means you'll have 12 months X 10 categories X 20 row entries = 2400 formulas, hardly a few.

For my own edification, I'm genuinely curious how to do this with just a couple formulas.
posted by achrise at 7:43 AM on March 27, 2019

To answer achrise's question and any other Excel aficionados reading this question:

If I am reading this correctly, it sounds like there is an 'input' sheet where all the entries are logged in individual rows. Each of these rows/ entries would need a unique identifier (separate column) which identifies Month, Category, and a count of the number of Month by Category. Like MarchGroceries1, MarchGroceries2, FebruaryGroceries1, February Groceries2, February Housing1 etc. This unique identifier can be generated by a formula.

(In general, creating a separate 'unique identifier' columns for most things fixes a lot of Excel reporting problems!)

Then there are 'report' sheets, in this case, broken down by months. These report sheets are prepopulated with the formulas (perhaps rows 2 to 500). These formulas are index match functions, which picks out the first, second, etc occurrences of Month, Category, Count. So MarchGroceries1 will get picked up with a special match function.

I guess when I say a few simple formulas, I mean it involves duplication of the said formulas, but no manual editing once the structure is in place..... Depends what you mean by simple :)
posted by moiraine at 8:16 AM on March 27, 2019

Just to add: You could even do something smart where the formula picks up the name of the sheet. Like the March sheet could pick up March in the name.

And yes, I imagine the workbook to contain 12 months x 10 categories x 20 row entries = 2400 formulas that are all the same, so just one formula in my mind.
posted by moiraine at 8:24 AM on March 27, 2019

Thanks moiraine, I picture your approach now. It seems pretty slick but there's one more thing I'm curious about. I believe the matching keys we're talking about (e.g. MarchGroceries1) have to be generated ahead of time in order to create the formulas in the Month sheets. and I understand that one can create a key with a formula from the expense row entries, but without additional info entered by jeudi I don't think you can properly come up with the "1" and "2".

A row for purchase of groceries on 3/27 might have a key that looks like "groceries43551" by concatenating the Category and Date. I just don't see how you can make them in the form CategoryN where N are integers starting at 1 and increasing monotonically. Now if jeudi adds another column and manually populates with an index number each time a purchase is entered it could work, but they can't be just 1,2,3,4,5, they have to be 1 for the first purchase of groceries in the month, 2 for the second purchase of groceries, ... so one might have to look at all the purchases for a category in a month when adding data to determine the correct index.

So how does one come up with the "unique identifiers" that can be hard-coded into the Month sheets based only on Date, Category and Amount entered in the Expense sheet?

(Even though I'm not the Ask-er, this is still very useful, thanks)
posted by achrise at 1:24 PM on March 27, 2019

(Apologies for the back-and-forth) Okay, I see what you mean. I just did something similar on an input sheet with two report sheets (let's say, it just has March and April only). It involved four extra columns of counting, two IF columns, and two SUM columns to count the occurrences. The fifth column was a concatenate of MarchGroceries[SUM]. So I can see how it's more complicated if there are more than two report sheets - a lot of IFs and maybe even nested IFs. Unless someone else has a better suggestion.

In that case, I would probably have to resort to a pivot table. I know, I know.

- The input sheet would still need a Unique Identifier column for each row -- random number formula or as I commonly do it, the formula 1, 2, 3, 4, etc down the column. Doesn't matter as long as the numbers in the column for each row is unique.

- The report sheets (in this case, Months) would contain a pivot table (usually I place this somewhere at the end, like column M or something) with just Unique Identifiers as entries, filtered by Month. A slicer would make this selection visual and interactive.

Then, on another column away from the pivot table, I would do a replication of Unique Identifiers. Like on cell A2, = M2 (where M2 refers to the first entry of the pivot table),
A3 = M3, and so forth.
Then I would Index Match the Unique Identifiers
posted by moiraine at 2:33 AM on March 28, 2019

Response by poster: Well, the impression I am getting from this is that it just isn't that simple. In my very limited experience (which I should have been more clear about, unfortunately -- it doesn't even cover Index Match and I have only just begun to figure out pivot tables, so I have been a bit out of my depth here!), I had been hoping there was some kind of formula I might be able to write to say "if the date in column A in any row of sheet Everything is in March, and column B in the same row contains Groceries, automatically append the value in column C of that row to column B [Groceries] on sheet March" ... but I get the impression that would require way more formulas than I was actually realizing! Thank you in any case.
posted by jeudi at 7:32 AM on March 29, 2019

OK, I had to prove to myself that my way, via macros, wasn't too bad; so here's a little one that does the job.

Setup: One workbook with 13 worksheets; one sheet called "Expenses" and one sheet for each month named as the month. The Expense sheet has 3 columns: Date, Category, and Value. The Month sheets have all the Categories enumerated across Row 1. All other rows are blank. The Categories can be in different columns in different months. There's a hardcoded limit of 26 Categories but you can change that.

When the macro runs it starts at the top of the Expense and steps through all rows, identifying the Month and Category of each entry, and then poking the corresponding Value into the first empty row in the Category Column in the Month sheet. The only downside is that it processes the entire Expense sheet at once. If you play with the starting row you could do this incrementally, say every month.

(This is pretty bare bones. Almost everything in the While loop can be combined into one statement, but it's reeeeeeally long. I split it up for legibility)

Sub DistributeExpenses()
ExpenseRow = 2
While Worksheets("Expenses").Range("A" & ExpenseRow).Value <> "" ' Loop until you hit an empty row
   ' Get a clean month name out of the expense date
    MonthS = MonthName(Month(Worksheets("Expenses").Range("A" & ExpenseRow).Value))
   ' Get a clean Category name for this expense
    CategoryValue = Worksheets("Expenses").Range("B" & ExpenseRow).Value
   ' Identify the column position for this Category in the appropriate Month sheet
    CatCol = Worksheets(MonthS).Range("A1:Z1").Find(CategoryValue).Column
   ' Identify the first empty row in the Category column
    CatRow = Worksheets(MonthS).Cells(Rows.Count, CatCol).End(xlUp).Row + 1
   ' Poke the value from the Expense sheet into the identified cell in the month sheet
   Worksheets(MonthS).Cells(CatRow, CatCol).Value = Worksheets("Expenses").Range("C" & ExpenseRow).Value
   ExpenseRow = ExpenseRow + 1
Wend
End Sub

posted by achrise at 12:03 PM on April 2, 2019 [1 favorite]