• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort Table Columns

navic

Member
If it is possible
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
Example:
- 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
 

Tech56

Member
Thanks for the reply. I figured out that if I make sure the Beginning Balances have the earliest dates and then it's no problem sorting the dates.

Cheers
 

Peter Bartholomew

Well-Known Member
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
 

Attachments

Tech56

Member
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.

Thank you so much
 

Attachments

Tech56

Member
Sorry my first reply was wrong. The Transactions sheet doesn't have a table above for the beginning balances. Is it necessary when sorting columns?
 

Peter Bartholomew

Well-Known Member
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])
will do.
 

Tech56

Member
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.
 

Attachments

Top