Surface Pro (2017) vs Surface Pro 4
I have an Excel workbook which consists of several sheets of accounts. Each sheet has a column for each of: date, description, debit, credit and balance. There's a formula in the balance column which takes the balance of the row above, adds the credits and substracts the debits of it's own line and then gives the current balance. Consequently, the current balance is always the last entry in the final column.
I'd like to set up another sheet to form a summary screen with a list of accounts and their balances. This means I need a way of getting Excel to go to each sheet, go down the last column until it finds the last entry and then report this back to the summary sheet.
I could do this by recording a macro, but that would mean I'd have to run the macro each time I changes anything on the main account sheets to ensure that the summary was always up to date.
Can anyone advise if it's possible to use a formula to achieve this so it would work in real-time?
PS: This is quite difficult to explain without being able to demonstrate! Please let me know if you think you could help but don't understand what I'm saying!! :-)
Open a new sheet, click in the cell were you want the results to be entered and place the = sign in it. Go to the sheet were the information is, click in the relevant cell and press enter. You should end up with something like this =Sheet1!A2
In doing this the new sheet will automatically update any figures created from the sub-sheets.
I hope this is what you are meaning?
Thanks to those who have replied. However, I think I mustn't have explained this clearly enough because you've misunderstood what I want to do.
The value that is being transferred is not always in the same cell (absolutely), but the location of the value is always the same (relatively) - it's in the last value-containing cell of column E. So, if 200 rows of sheetXYZ are filled, then the balance to be reported from sheetXYZ is in E200. But, if another transaction gets added to this sheet, the value is then in E201, and so on...
I think an example might help so I've made an annotated copy of a sheet to show how it works.
Please click here to get a copy. I think/hope it will be easier to understand if you can actually see the sheet!
One method of doing this would be to assign a name to each of the cells to be included in the summary (Select the cell then Insert\Name\Define). Then you only need to refer to this name in the summary sheet.
But I can't name a specific cell and refer to that because the cell isn't always that same?
I'm thinking another way to go about it might be to have the summary sheet perform all the calculations again. I.e. the summary sheet would have to take the opening balance of all the accounts, add to this the contents of column D on the sheet the balance is coming from (i.e. all the credits to the balance since the starting balance) and subtract all column C (i.e. all the debits since the account began).
This seems a bit long winded, but at least it would work...
Normally I just estimate the maximum number of rows and then sum the credit and debit columns from which it is easy to calculate the closing balance in a known absolute referenced cell. If you don't want to display the row either hide it or keep it below the print area.
A second more elegant approach is to use "conditional formatting". Proceedure as follows:
1) Estimate the maximum number of rows
2) Copy down the balance formula (so the final balance will be displayed against all the blank rows.
3) Go to cell E4, select Format - Conditional Formatting and select "Formula Is" in drop down box under "condition 1"
4) Enter the following formula: =ISBLANK(B4)
5) Click the Format box and set the font colour to white.
6) OK twice and copy down the formatting to the remaining balance entries
You will no longer have to copy the balance formula each time you add an entry, only transactions with a description entered will be displayed (you could extend to numbers in credit and debit columns if you like by adding additional conditions)and you can collect up your final balance from a known cell reference.
Hope this helps
Is it possible to change how the sheets are designed in the first place.
I believe if you had all debits in column B (from row 2 down), and all credits in column C (from row 2 down). You could then have the total value of debits in B2, total value of credits in C2. If you had the starting balance in A2. Then in D2 enter =A2-B2+C2.
Then D2 is your absolute cell each time to use to link to your summary sheet.
You can still then have a running balance in column E if you want that to be visible as well.
Sorry the total value of debits in B1, total value of credits in C1, starting balance A1, and D1 then is your absolute cell reference using =a1-b1+c1.
If you want a headings row for each column these can go in row 2, just start the transactions from row 3.
I have found that this upside down method of having totals at the top solves a lot of issues for me.
If you record a macro to do this, the macro will contain absolute cell addresses. However, you can modify the code to find the cell in the last row. The following code finds the last row containing data:
LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row
This thread is now locked and can not be replied to.