by Wayne: Excel allows sorting of selections inside of a range but will always sort an entire table. Also, it would likely be simpler to convert the table to a range, sort the selection, and reformat as a table
- Select cells A12:A44
- Click on "Home" in the menu bar
- Click in the "Editing" tab, on "Sort & Filter"
- Select "Custom Sort"
- Uncheck the box "My data has headers"
- Sort By: Column ???
- Sort On: Values
- Order: A to Z
- Click OK
I have included two tables that allow the main table to be sorted whilst retaining the initialisation data at the top of the sheet. Some of you balance of transactions to date are unusual in that they have to scan over both tables.
Since you have tables, something you may care to consider is the use of structured references in place of direct cell referencing (see workbook). Either way, I was concerned to see formulas that contain a reference to a final cell in a range, expressed in the form = OFFSET(A9,0,0,1,1)
Others may have some different views but to me it is just a roundabout way of saying = A9
Thank you Peter and I agree with the formula references. I started this workbook from a template and have tried to adapt for my needs. Since requesting the last help I made some changes to the workbook. I included the running balances for the Credit Cards at the top of the Transactions sheet so I can see which ones may have excessive amounts so I can stay on budget.
Can you do the same with this workbook. I am scared to mess it up for functioning.
If you can do without the separate table, that would simplify the formula logic. If you need to sort the table, sorting on date is essential to make sense running balances so you would need a multi-column sort that could put the beginning balances back to the top. I also notices that the SUMIF formula used "="&[Account] The "="& is surplus to requirements = SUMIF(Transactions[[#Headers],[Account]]:[@Account], [@Account], ……......……...Transactions[[#Headers],[DEPOSIT]]:[@DEPOSIT]) - ….SUMIF(Transactions[[#Headers],[Account]]:[@Account], [@Account], ….........……...Transactions[[#Headers],[PAYMENT]]:[@PAYMENT])
I altered the Transaction sheet to only have one table. I have different formulas for the updated balances on the top right and the initial balances are just ranges instead of a separate table. The overall amount from these initial balances are added to the first row BALANCE column (N19) only. I hope this method is accurate.