Sort Table Columns

[Tech56]

Hi,

On my Transactions sheet how can I sort the columns from row 12 and below so the Beginning Balances in green don't get sorted?

Thank you

 

[navic]

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]

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]

Or create a another table stacked above the first.
The second table doesn't need to show headers and any blank separator rows can be hidden.

 

[Tech56]

Peter can you please show me how to do this? I don't want the formulas to not function.

 

[Peter Bartholomew]

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

 

[Tech56]

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

 

[Peter Bartholomew]

I have introduced some formulas. I overwrote the previous version on my machine so you will need to reinstate the correct version number.

 

[Tech56]

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?

 

[Tech56]

Maybe this layout is better so there aren't hidden rows? I appreciate your advice thanks.

 

[Tech56]

Disregard I figured it out thanks.

 

[Peter Bartholomew]

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]

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.