Forum Discussion
Excel formula help... again!
I have different tabs for each month.
I need the total of the last day of month to start in the opening balance on the next tab in the beginning of the month. but some months obviously has 30 days and some 31 and then there's Feb too doing whatever it wants ;-)
the total here in AH has to be the last day of the month - here it's the 30th and 379
the opening balance for the next tab (yellow block) has to be whatever the previous month's closing total was.
I don't know how to create a formula to choose the last day's total and pull it to the new sheet in the opening balance.
Thanks so much
2 Replies
a) I would recommend not having separate tabs for month but have a running tally in the main tab and then you can pivot table or filter or create custom report tables as needed
b) If you stay with the current why not just have B3 = PriorSheet!AH3 and months with <31 days just don't use column AG as shown in the image. if the question is how to get the value from AF to AH then:
AH3 = MAX(AD3:AG3) [this assumes the values are always increasing]
or
AH3 = TAKE(AD3:.AG3, , -1) [this is the same as =TAKE(TRIMRANGE(AD3:AG3), , -1)
c) if you are trying to find a automatic way to reference the prior sheet without manually entering the formula you can use:
B3 = IF(SHEET()>1, INDEX(TOROW( 'FirstSheet:LastSheet'!AH3 ), SHEET() -1), 0)
TOTALLY concur with m_tarler : it's generally a design mistake (in the sense that it makes things more complicated and is more error prone) to have a separate sheet for each month. This often happens because people carry-over the ledger sheet mentality to Excel, rather than taking advantage of Excel's superior ability to work from a single comprehensive datatable, producing monthly reports as needed, but also readily showing current status--whatever you want.
Are you open to reconsidering your design? If so, please give us a more complete picture of the application, ideally including an anonymized copy of the actual workbook.
