Howdy Ho! I need some help with an excel spreadsheet. What I need to do is create three different sorts and I can choose which one by pressing a button
Option one is Sorting by Largest Balance
Option two is Sorting by Smallest Balance
Option three is Sorting by Highest APR
I have a spreadsheet with dummy data on mediafire
All help is appreciated
Is sorting by largest and smallest balance the same sort except in reverse order?
If you want to do it by pushing a button, the simplest way is to record three macros and assign them buttons. There should be a wizard in any version you may be using that will step you through, but basically you hit record macro… do the sort like you normally would, hit end record, then it should give you options… one of which should be assign to a button. You can then put that anywhere on your spreadsheet you want.
This is actually pretty easy. Let’s start with – which version of excel do you have?
What you want to do is:
So, which of these things do you know how to do, and which do you need help with?
Or throw the whole thing in Access and create three different reports. 
I agree - creating macros for each sort is the easiest. Although it’s hardly more difficult that highlighting the column you want to sort and click the A-Z (or Z-A) button.
StG
Yes I believe it is
My problem with making a macro is if I highlight A1 through E17 and try to do a sort on balance it works almost right and I forgot to put this requirement in my original post. If the balance is 0 it ignores it completely
I don’t know how to select a named range of cells. I do know how to add a radio button and have it run a macro
I don’t want to deal with access.
First of all, don’t leave a cell in the data range empty. If the balance is $0, put in 0, don’t leave it blank. I see that was the case in your sample data, and it might be causing sort problems.
If your formula bar is visible – I believe by default it is – there is a blank field to the left of it. Type the name of your cell range there, hit enter, and that range will be selected. Typing in the name there will be the very first command of each macro, to select the range to sort.
I think that takes care of everything you need, unless I’ve missed something.
What do I do about rows that have no data currently but will if I add additional cards in the future? I don’t wan’t to have 0’s just hanging out there which makes it look messier than it is now.
What do you mean my I found the field thanks
That should be it for now until I think of other things I want to do.
If it was me, I would just add rows as I actually got data for them, and redefine the range name to include any more rows you add. So long as you stick with the same name, you won’t need to do any more editing of the macros.
I think there’s a way to tell Excel to treat blank cells in a column formatted as numbers as if they are zeroes, though I don’t recall that at the moment. It will have one effect that might or might not be a problem for you. Let’s say you have 4 rows of data, but you’ve defined your range to include 50 rows for later expansion. If you sort by the large range in ascending order, the actual data will display in the bottom 4 rows.
I don’t want to add rows as I need them because I plan to give this spreadsheet to others. However I wonder if there is a way to make it hide a row if the first column is blank. Then I could make a separate tab that only has actual accounts
Is the balance “0” or is the cell empty? They are not the same thing.
Cells A-E would be blank and G would have 0 in it because the formula I am using is
=IF({cell with balance}>0,{cell with balance}/{credit limit},“0”)
Of course {cell with balance} and {credit limit} are actual cell like C2 I have to do this to avoid division by zero displays.
I’m not sure what you mean by “tab” in this context. Are you saying that somewhere else on the worksheet, or on another page in the workbook, you will enter the account names, and set the values in the actual data range name column to equal the names you entered elsewhere?
I should use the term worksheet. I’m just trying to think of a way to make this less confusing for others if I give it out to people. My brain can handle the spaghetti logic I think with but other’s cant.
I’m not picturing this clearly. You can throw in a nested if statement so the calculation is only performed if the denominator is >0, and if it =0 (or empty), then define the result as “”, which means nothing displays. Does that solve your problem?
It may. May I take this to Email with you?
You can filter out rows where the value in a given column is blank. Go to Data, Filter, Autofilter. A little arrow will show up next to the column headers. You can select to autofilter out any rows in columm A if that fit your criteria. You’re going to have to play some with that criteria, because if you are using equations in the cells in that column to reference something else, then it won’t be a matter of filtering blank cells because they won’t be blank. They will have equations in them.
You could instead add another couple of macros and button to “update” and "save changes to both your data and your cell range. Users hit the “update” button to add new rows of data, I don’t expect it would be needed to change any numbers within the existing range. They add their new rows, and hit a “save” button, which doesn’t really save it so much as it redefines your cell label to expand to the include the new rows. Your can select the cell in the upper left corner of the range, and tell it to select over to the right-most and bottom-most active cells, and then define that range again with the same range name, all within the macro.
This route, no weird sorting results due to empty rows, and no indirect references to account names on a list somewhere else.
I sent you a PM with my email.
I’m glad this came up. I recently “upgraded” from Excel 2003, and frankly it feels more like a downgrade, so I need the practice.
Ok, Manny I have your spreadsheet. I took a quick look but I’m about to crash.
Couple things… what do you mean the sort “almost” works?
What I see is, I think, good news. Your problem is not the nearly blank rows with nothing filled in but the last two columns. The problem is rows 12-17, with the vendors you presumably already have accounts with, but no outstanding balance. Those need to be changed from blank to “0”. If you do that, the list sorts fine in both directions, and the rows with banks in the Balance column stay at the bottom. This works even if you select the range of cells all the way down to row 35, which presumably is how far you thought the list could expand to. I’m basing that on how far down you extended your calculations for “Pmt Due?” and “Payments @ Min”.
I would also change the formulas in those two columns. Note how in rows 12-17, it says YES, payment is due, even though there is no balance. I would change them both to some kind of compound AND formula, to do your calculation only if there are values in at least one of the calculated fields, and to display a blank cell if the rest of the data in that row doesn’t exist.
I’m not thinking clearly enough to work on formulas any more tonight, but I’ll give it a shot tomorrow if you need help with changes.
But really, filling on “0” in those five rows will solve all your sorting problems, and you can define the cell range down to row 35 right at the start so the macros will sort that whole area. Just make sure that when you add any new rows, not to leave the balance or APR fields blank.