Excel: How to select a cell based on current month

November 23, 2007 9:40 AM

Excel Filter: Selecting a cell/row based on current month.

I'm putting together a payment spreadsheet for a very small business, and I'm piecing it together slowly. I'm sure there are easier/elegant solutions abound, but this is what I have so far.

In this example file:

www.buildinbranson.com/mefi/mefiExample.xls
or
(Screenshot if you can just work from that)

I have a column of dates and a column of current balance (Starting at A9 and B9). I want the Total Unit Due in cell C5 to look to the current month's balance.

I've played around with a formula to compare the current month to the month from column A*, and that works just fine. What I can't figure out is how to make it select the appropriate row.

Sorry if this isn't overly clear, I'll try to clarify as needed. (Microsoft Excel 2007)

posted by shinynewnick to computers & internet (5 comments total)

You can use a DGET function.

In A8 and E4, type in "Month". In E5, input the following formula to generate the first day of the current month.

=DATE(YEAR(TODAY()),MONTH(TODAY()),1) .

In C5, input the following formula to retrieve the current balance in the corresponding month.

=DGET(A9:B26,"Current Balance",E4:E5)

Now if you move A9:B26 to their own sheet (Sheet2) so that they start from row 1, you can replace A9:B26 with Sheet2!A:B and then the formula will work independent of any new rows you add.
posted by junesix at 10:10 AM on November 23, 2007

BTW after you move A9:B26 to Sheet2 starting in row 1, your Total Unit Due formula in C5 should look like this:

=DGET(Sheet2!A:B,"Current Balance",E4:E5)

What it's doing is looking in the A and B columns in Sheet2, focusing on the "Current Balance" column, and then matching it against the criteria in E4:E5 which is the first day of the current month.
posted by junesix at 10:13 AM on November 23, 2007

I'm not familiar with DGET, but I tried junesix's example above so I didn't waste any time replying if the problem was already solved. I might've done something wrong, but it didn't work, so here's what I propose:

Put "Current Month" in E4, and in E5, use junesix's formula for putting today's date/month:

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

This should end up being "11/1/2007" as of the time of this posting.



Next, use the LOOKUP function in C5:

=LOOKUP(E5,A9:A25,B9:B25)

This loks for what's in cell E5 within the range of cells A9:A25... and returns whatever is to the right of that in the range of B9:B25. So, since $40 is to the right of 11/1/2007 - this returns $40.
posted by twiggy at 10:20 AM on November 23, 2007

Thanks so much, that worked perfectly, twiggy.
posted by shinynewnick at 10:32 AM on November 23, 2007

Strange, it had worked in my sheet. Not sure what happened. In any case, glad you got a working solution.
posted by junesix at 11:24 PM on November 23, 2007