Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to last row with data in it
I could write a VBA macro to do this, but I'm hoping there is a
simpler method. I've got a spreadsheet with several bank statements in it. Column A of each sheet with a bank statement is dates, column C is the account balance. On the cover page I've got a summary table with hyperlinks to each of the respective bank accounts, I've got something close to what I want by using formulae similar to the following =VLOOKUP(MAX('ANZ Bank Statement'!A:A),'ANZ Bank Statement'!A:C, 3,FALSE) i.e. look up the value in the third row which is next to the latest date. Works fine if the latest day has only a single transaction, but if there were several transactions that day it will show the first of the final day's transactions, not the last of the final day's transactions, so my formula doesn't necessarily show the latest value of the bank account. I spent a bit of time yesterday writing a complex looping macro for a different problem before realising that I could achieve the same result by using Excel 2007's SUMIFS function. This was good because it meant the table would auto update without having to run a macro or set a resource-hogging "on change" event trigger or something. But I'm trying to figure out how to get it to always refer to the last of the rows. The following SUMIFS will add up all the values on the last date, which isn't what I want. I could add a second condition though, how would I specify "and if the value in the following row is blank" or some equivalent condition? =SUMIFS('ANZ Bank Statement'!C:C,'ANZ Bank Statement'!A:A,MAX('ANZ Bank Statement'!A:A)) Travis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to last row with data in it
Hi,
I may be missing something here but on the assumption dates are entered sequentially in column A don't you really want the last value in Column C regardless of date. if so try this =LOOKUP(10^23,'ANZ Bank Statement'!C:C) Now if someone has more than 10^23 dollars/pounds in the account this fails :) Mike "travis" wrote: I could write a VBA macro to do this, but I'm hoping there is a simpler method. I've got a spreadsheet with several bank statements in it. Column A of each sheet with a bank statement is dates, column C is the account balance. On the cover page I've got a summary table with hyperlinks to each of the respective bank accounts, I've got something close to what I want by using formulae similar to the following =VLOOKUP(MAX('ANZ Bank Statement'!A:A),'ANZ Bank Statement'!A:C, 3,FALSE) i.e. look up the value in the third row which is next to the latest date. Works fine if the latest day has only a single transaction, but if there were several transactions that day it will show the first of the final day's transactions, not the last of the final day's transactions, so my formula doesn't necessarily show the latest value of the bank account. I spent a bit of time yesterday writing a complex looping macro for a different problem before realising that I could achieve the same result by using Excel 2007's SUMIFS function. This was good because it meant the table would auto update without having to run a macro or set a resource-hogging "on change" event trigger or something. But I'm trying to figure out how to get it to always refer to the last of the rows. The following SUMIFS will add up all the values on the last date, which isn't what I want. I could add a second condition though, how would I specify "and if the value in the following row is blank" or some equivalent condition? =SUMIFS('ANZ Bank Statement'!C:C,'ANZ Bank Statement'!A:A,MAX('ANZ Bank Statement'!A:A)) Travis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to last row with data in it
On Oct 15, 11:21*pm, Mike H wrote:
Hi, I may be missing something here but on the assumption dates are entered sequentially in column A don't you really want the last value in Column C regardless of date. Yep if so try this =LOOKUP(10^23,'ANZ Bank Statement'!C:C) Now if someone has more than 10^23 dollars/pounds in the account this fails :) It worked. Thanks. Now I'm curious about *why* that worked!? Travis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to last row with data in it
Hi,
Glad I could help. It worked because 10^23 is a very large number that I was fairly confident wouldn't be found in Column C. Because it didn't find it the formula returned the last numeric value. Mike "travis" wrote: On Oct 15, 11:21 pm, Mike H wrote: Hi, I may be missing something here but on the assumption dates are entered sequentially in column A don't you really want the last value in Column C regardless of date. Yep if so try this =LOOKUP(10^23,'ANZ Bank Statement'!C:C) Now if someone has more than 10^23 dollars/pounds in the account this fails :) It worked. Thanks. Now I'm curious about *why* that worked!? Travis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to last row with data in it
And by the way there is a related problem which I'm not sure can be
solved as simply as the lookup(big number,range) trick. I want to produce a table showing the closing account balance at the end of every month. There may be no transaction between the 20th and the 3rd, but I'm not worried about accrued interest so in that case I'd take the value on the 20th. This would enable me to show my historical end of month cash levels. Travis |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to last row with data in it
I believe this array-entered** formula will do what you want...
=INDEX(C:C,MATCH(ROW(A1),MONTH(A$1:A$2500))) ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Place the formula in a cell, change the 2500 row reference to the maximum row number you ever expect to have data in, and then copy it down for 11 more rows. -- Rick (MVP - Excel) "travis" wrote in message ... And by the way there is a related problem which I'm not sure can be solved as simply as the lookup(big number,range) trick. I want to produce a table showing the closing account balance at the end of every month. There may be no transaction between the 20th and the 3rd, but I'm not worried about accrued interest so in that case I'd take the value on the 20th. This would enable me to show my historical end of month cash levels. Travis |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to last row with data in it
On Oct 15, 11:53*pm, travis wrote:
This would enable me to show my historical end of month cash levels. Actually, I figured this one out for myself. Inspired by your lookup idea, I found that vlookup(EOMonth(date),range,offset,TRUE) gave me the answer I was looking for. Travis |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to last row with data in it
On Oct 16, 2:59*am, "Rick Rothstein"
wrote: I believe this array-entered** formula will do what you want... =INDEX(C:C,MATCH(ROW(A1),MONTH(A$1:A$2500))) ** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself Place the formula in a cell, change the 2500 row reference to the maximum row number you ever expect to have data in, and then copy it down for 11 more rows. How would I do this with VBA? Travis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to data series in a chart | Excel Programming | |||
Referring to external workbook in Data Validation | Excel Discussion (Misc queries) | |||
referring to other worksheet | Excel Worksheet Functions | |||
Pivot Tables referring to external data query | Excel Discussion (Misc queries) | |||
Referring Date in VBA | Excel Programming |