You are here:

Advertisement

Hi Bob!

I use Windows 7, and Excel 2003, 2010, 2013.

Below, I simulate a spreadsheet...

column F column G column H

row 8 1 5 6

row 9 2 6 8

row 10 3 7 10

row 11 4 8 12

columns F AND G contain raw numbers. Column H, row 8 contains a formula:

=sum(f8:g8)

I copy the cell H8 to rows 9-11 and the result is seen above. So far, so good.

I then select cells H8 through H11, move to sort, and I'm asked whether I want ascending or descending in column H. I click the Descending button, click OK and then I'm asked whether to:

1-expand the selection

2-use only the selected column (Column H)

Here is the problem:

If I choose to sort only column H, NOTHING HAPPENS! NO SORTING TAKES PLACE!

If I choose to expand the selection to include columns F and G, the result is ALL THREE COLUMNS ARE SORTED IN DESCENDING ORDER , which I don't want.

What I want is the result below:

column F column G column H

row 8 1 5 12

row 9 2 6 10

row 10 3 7 8

row 11 4 8 6

i.e., only the last column is sorted. Microsoft gave me a choice of expanding or not expanding. I chose not to expand the selection and found that no sorting would occur.

Being intrepid, I found a solution, but it doesn't get Microsoft off the hook!

Back to the first spreadsheet above...

If the formula in cell H8 is =sum(Sheet1!f8:g8), the expected result, "6" is found in cell H8. I then copy the formula to the other 3 rows.

The screen now appears as in the first spreadsheet above.

Now, when I proceed to sort, and limit myself to selecting only

column H, do I achieve my goal, seen in the second spreadsheet above.

Let me hear what you think of all this.

Thank you.

Chris

Certainly is unexpected results. When you use F8:G8 and do the sort, if you watch carefully, you will see that it sorts as you want at first, then the formulas "kick in" and the calculation is "restored". If you turn off automatic calculation,you will see 12,10,8,6 as you wanted, but once any calculation is done, the order will be 6,8,10,12 again.

What's unexpected for me is having Sheet1!... make any difference.

Being an MVP, I have many contacts who can also look at this and give their interpretation, and I'll get back to you again. It'll be awhile.

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."**Publications**

Excellence, The Expert, Microsoft**Education/Credentials**

BA in math, Hofstra University, 1965**Awards and Honors**

MVP

Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks