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
5 Replies
I just don't understand where my reply went. This is so annoying!
The values changes, I have calculations on the cells in the cows in milk (CIM) row, the calculations are as follow:
previous day minus cows dried off minus CIM died minus CIM sold plus dry cows calved plus heifers calvedthe total in the yellow block on the right (CIM row) has to be of the last day of the month and then has to be the opening balance (CIM row yellow block on the left) for the next month as the CIM row calculations will start with the opening balance value.
I just don't understand where my reply went. This is so annoying!
The values changes, I have calculations on the cells in the cows in milk (CIM) row, the calculations are as follow:
previous day minus cows dried off minus CIM died minus CIM sold plus dry cows calved plus heifers calvedthe total in the yellow block on the right (CIM row) has to be of the last day of the month and then has to be the opening balance (CIM row yellow block on the left) for the next month as the CIM row calculations will start with the opening balance value.
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.
No the values does not increase, it differs, there are a few calculations I have on each cell already.
And the different sheets is just to organize it better for the client I'm trying to help.
I'll send a screenshot of what it is I'm working with:calculations include - each cows dried off cell deducts from cows in milk (CIM) and each dry cows calved adds to CIM again.
the total in the yellow block on the right needs to be the starting total for the next month in the opening balance as the calculations add and subtract again for the next month.
