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

Sorting on a column that includes a formula scrambles the formula

2

2n37

New Member
I've provided a scaled down version of a larger spreadsheet. The issue that I describe below is that formulas that include the active sheet name in the formula get scrambled when sorting.

Looking at B10:B33, you'll see we have a fairly standard INDEX/MATCH combination that evaluates the year and the adjacent country and returns the correct value from the Value sheet.

Looking at C10:C33, you'll see we have essentially the same formula doing the same thing as column B, except for the cells being referenced on this Summary sheet don't have the Summary! in front of them, where as the column B formulas do.

Now for the issue:

If you select A8:C33 and sort on Column B, Excel scrambles the results (specifically scrambles the formulas in column B). Now undo the sort.

If you select A8:C33 and sort on Column C, Excel handles the sort without issue.

Any ideas what is going on and how to get around it without having to go manually delete the sheet reference as I did in Column C? Thanks!

-Erik
 

Attachments

Thanks Luke M. I teach an introductory Excel course (this is part of a homework assignment). I haven't yet sorted out why some students' end up with the sheet reference and others don't. I'm guessing it has something to do with the mouse clicking techniques (or lack of).

-Erik
 
Hi Erik,

Good stuff. You're correct, it's usually how students are clicking on sheets. The most common instance I see is where you build a formula that has an earlier portion referring to a different sheet. So, start typing a formula, navigate to a cell on different sheet, and then go back and click on a cell in same sheet as formula. Formula will auto-write the sheet name. IMO, pretty annoying. :(
 
Thanks Luke. I'll just make this homework more involved by using the existing plus a rank as a staging area and then a final area that uses another index/match to put them in order as the final presentation. This is ultimately a vlookup/index/match assignment, so they'll just get a little more practice. Not the worst thing.

-Erik
 
You must log in or register to reply here.
Back
Top