aaaarg! Excel can bite me!

[telophase]

Anyone here familiar enough with Excel (2000, in this case) to tell me how to do this?

I've got a spreadsheet with the results of the ConDFW art show sales. One of the worksheets in this spreadsheet is totals of sales by bidder number - I've got a formula that goes to the worksheet with the full data, looks for rows that contain a particular bidder number, and adds them up. The formula looks like this:

=SUMIF(Sales!$C$2:$C$140,'Bidder Totals'!A2,Sales!$D$2:$D$140)

where A2 (A3 in the next row, A4 after that, etc.) is the bidder number. The results in the Bidder Totals table look like this:

Bidder #	Total Sales
01	30.00
02	0.00
03	61.00

The problem comes when I want to sort the results by total sales, because I want to see who spent the most money in the art show, in order from most to least. Unfortunately, stupid Excel sorts the Total Sales column (which contains the formula) by the text of the formula, and not the results of the formula, which is TOTALLY STUPID. Why would I want to sort by "Sales!$C2:$C$140..." and not, say, "$ 30.00"?

Any help is WELCOMED. I'm going to find something to eat and check my mail, which ahsn't been looked at in four days. Hopefully somethign interesting is in it.

Comments

[vom-marlowe.livejournal.com]

Hmm. I can make Excel do pretty much anything, when it's right in front of me. I can take a look at it for you, if you want to email it to me. In the meantime, I'll try mocking up what you've got here and get back to you. Should this fail, for any reason, one of my employees is a professor of excel (really!), and I can get her to smack it around tomorrow if you'd like.

So far the interwebs were pretty quiet, I think. I do still have plenty of Passion if you'd like some. (Okay, I just couldn't help myself from typing that~! Hee.) But seriously, I do have some extra and impable bottles.

[gweniveeve.livejournal.com]

My first thought was Excel Saga, which is much more fun than Microsoft Excel... (for me, anyway!)

[lanisatu.livejournal.com]

I don't really use Excel much myself, but maybe you will find this site useful:
Mr. Excel (http://www.mrexcel.com/)

They also have a forum (http://www.mrexcel.com/board2/) where you can ask questions. I hope that helps.

[selenite.livejournal.com]

Hmmm. 2000 doesn't support sorting with autofilter, but you may want to try turning it on (Data menu/Filter/Autofilter) and going to the menu for the Total Sales column to see if it gives you a sort option.

The regular sort option is going by values rather than formulas in my quick test setup, so there's probably a flag set somewhere to look at the formula. The dirty solution is to paste the values of the Totals column in a new column and sort by that.

[vom-marlowe.livejournal.com]

Okay...can you tell me what your sort dialog box looks like?

[telophase.livejournal.com]

Emailed.

The sort dialog box is pretty simple - 3 options to choose columns for sorting data, asc or desc in each case, and radio buttons for Header Row and No Header Row. The Options... button pulls up a box with a drop-down menu titled First key sort order, and the choices are days of the week and months. It also has a Case Sensitive checkbox and a choice of oreintation - sorting top to bottom or left to right.

[telophase.livejournal.com]

The autofilter menu doens't help.

If I paste the values into a new column and sort by that ... the sort somehow messes up the formulas in the original column and my totals there go off. AARGH.

[telophase.livejournal.com]

The autofilter menu doens't help.

If I paste the values into a new column and sort by that ... the sort somehow messes up the formulas in the original column and my totals there go off. AARGH.

[vom-marlowe.livejournal.com]

Sorry, didn't see the spreadsheet hit my inbox--you have very fleet fingers! :) I have sent it back, whapped into submission, I think.

[jspurlin.livejournal.com]

when you paste the values, right click and use "paste special" and select "values". that will paste just the numeric values and not the formulas-with-cell-references. that might be a workaround for now...

[telophase.livejournal.com]

That's exactly what I did - and the /sort/ messed up the original formulas, so the total amount sold changed to a different amount. :)

[jspurlin.livejournal.com]

hmm. hard to say-- like the other person said-- without the sheet in front of me. did they get it fixed for you? I'll take a look at it if you want...

[selenite.livejournal.com]

/me scratches head

If vom_marlowe's take doesn't fix it go ahead and shoot me a copy and I'll see what I can do with it.

[tprjones.livejournal.com]

I don't think there's an easy way to do it directly. But you CAN do it with an intermediary sheet.

Copy the summary sheet. On one copy (which will serve as an intermediary) insert a column into column A, and put in this formula on each line:

=RANK(C2,C:C)

Where, of course, C2 refers to that particular row's Total Sales (presuming they are in column C now). That formula gives you a ranking from highest (being 1) to lowest for the Total Sales.

Now, for the next otehr that gives your REAL summary, in the first column put the numbers 1, 2, etc to the number of Bidders (for the rankings). In the second column enter these formulas on the first row of beside #1 in the next two columns respectively:

=VLOOKUP(A2,Totals!A:C,2,FALSE)
=VLOOKUP(A2,Totals!A:C,3,FALSE)

This will look in column A on the sheet named Totals (the name I've given to that otehr intermediary sheet for this example) for the ranking number on the current row, and pull in the info from the specified column (2 and 3 respectively) from the other sheet, thus bringing in the Bidder # and Total Sales for each line. And when it's all done, they're ranked in order from highest to lowest automatically, and more imporantly it's dynamic and will change with new data entry (except the length as you'll have to add more rows as you get more bidders or you'll lose the lowest ones).

Easy peasy!

If you don't want to do it with an intermediary totals page, it can be done with SUMIFs and rankings directly on the data sheet to pull from. Or you could put in a macro to resort each time new data is entered, but that gets annoying as it fires off ever time you touch a cell. An intermediary is really the simplest, and you can even hide it if it's bothering you to have more pages.

[tprjones.livejournal.com]

Um, "Now, for the next otehr" == Now, for the next sheet" == me smokey too much cracky.

[darksumomo.livejournal.com]

Same here. Hail Ilpalazzo!

[darksumomo.livejournal.com]

W00t! Another Girl Genius fan!

[gweniveeve.livejournal.com]

*pulls rope*

*wishes she had Excel Saga icons*

[telophase.livejournal.com]

She was able to sort them in order - and to a quick glance the formulas look messed up in the same way as in mine, but weirdly the totals looks the same. Huh. At any rate, I've got another file with the sort, so I can find the top bidders easily.

Although if you're interested in it from the problem-solving viewpoint, I could send you a copy. XD

[(Anonymous)]

Is it wrong that I get so very much fun from Microsoft Excel? :)

[tprjones.livejournal.com]

Oops, that was me. Darn cookie-death.

[gweniveeve.livejournal.com]

If I had any ability at Excel, I might too. But alas, even Word makes me want to bang my brains out sometimes.

[selenite.livejournal.com]

I live for solving problems. This is why I play freelance tech support for my co-workers, since my job description no longer includes that, grrr.

[telophase.livejournal.com]

On its way. :)

[llamameeljueves.livejournal.com]

An easy way to solve it:

1) Make a duplicate of the whole worksheet (drag it besides holding CTRL key)
1) Select the items (numbers in your case) the row or column you want to sort.
2) Select Copy (CTRL C)
3) Right click in the same column or row and select PASTE SPECIAL, and select "VALUE" in the paste options dialog box
4) Click OK
5) Do the sorting, and this time should work because you shouldn't have the formulas but only the values.

XD