Share via

Excel table with formulas won't sort properly

Anonymous
2013-11-06T15:29:43+00:00

I have a formatted table and want to sort the data by one of the columns. All of the cells contain formulas because these are the calculation sheets for a dashboard I'm building. When I try to sort by one of the columns, it borks my whole table. You can see in this screenshot that I have sorted the table by Visits, but it won't sort. However, if I turn calculation option to manual, it works like a charm. However, if at any point I press F9, it blows up again, which means it will blow up for the client.

Any ideas on how to resolve this w/o converting the data to values? I can't do that, or I will lose all the functionality of the dashboard.

Thanks!

Annie

PS I'm working in 2013 but have the same problem in 2010.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-11-06T16:34:24+00:00

    Your screenshot truncated one important piece of information; the worksheet name. I think that the worksheet you are on is named Calculated Data - XXX and this is used within your SUMIFS() formula. A Sort does not carry any cell reference that is prefixed by a worksheet name so you should remove the local worksheet name from the last section of the formula.

    'Calculated Data - XXX'!$B13

       ... should be,

    $B13

    The earlier reference to 'Calculated Data - XXX'!$E$4 should be left alone as it is outside the block of sorted values and should not change with the sort.

    80+ people found this answer helpful.
    0 comments
  2. Anonymous
    2013-11-06T20:28:22+00:00

    You'll want to use structured table references here. There is a 'bug' in Excel (that's what I call it, others will tell you it's "by design", but I don't like the "design"). Check out the blog post I wrote on it. (http://alturl.com/eifm4)

    Setting the ranges, if they're dynamic, can be tricky, and most often make the formula longer, but it's worth it. It doesn't matter if you're in 2010 or 2013, the formula structure will be the same.

    Is there any way you can post the formula?

    4 people found this answer helpful.
    0 comments
  3. Anonymous
    2013-11-08T17:31:58+00:00

    Heh. I can honestly say I don't think I've ever used the formula wizard. But you're right; it's one of those conventions MS kind of forces on us. Blech.

    3 people found this answer helpful.
    0 comments
  4. Anonymous
    2013-11-07T13:44:37+00:00

    Hey Jeeped,

    Great point. I thought I gutted all of those from my spreadsheet. Microsoft keeps adding them back in. I broke my long formulas up into different lines in the formula bar to make these stand out better. Thanks!

    0 comments
  5. Anonymous
    2013-11-06T23:38:57+00:00

    Thank you for your reply, Zack! I figured out what the problem was. I had a catch-all row for "other" that I was using a SUM formula to calculate. When I removed that, everything worked as it should. I chose to do that over having a haphazard chart. I was missing the forest for the trees.

    Because of a tool I'm using to pull API data in, I can't use tables. I learned through trial and error that they caused my file to become corrupted, though I absolutely adore them and encourage marketers to \[almost\] always use them. But I totally agree that structured references > static.

    0 comments