How do I change my Excel array formula to allow for a Sort operation ?

Posted on 2012-09-08
Medium Priority
Last Modified: 2012-09-09
Hi Experts,

I have a workbook which contains child session information at a Nursery school. There are 3 sheets : Name, Calendar, Sessions.

The Name sheet simply holds a list of children's names and dates.
The Calendar sheet holds the session codes for the children. For example, a code 3 against a child's name on a specific day means the child will be attending the Under 3s class.
The Sessions sheet totals the number of sessions each child attends against a given code for each month.

The problem occurs if I try to manually sort the Child names in alphabetical order in all 3 sheets. When I come to sorting the list in the Sessions sheet the cell references in the array formulas don't change, therefore the numbers calculated refer to the wrong rows.

I tried using the INDIRECT and ROW functions in the FIND section of my array formula so it would pick up the ID value for that row but all it returned was a value of 0.

The example attached is a cutdown version as there is much sensitive information in the original, but the same principle applies to both.

Can someone suggest a Sessions sheet array formula which allows me to sort the data on the Name column of that sheet ?

Hope that is clear.

Question by:Tocogroup
  • 4
  • 3
  • 2
LVL 24

Expert Comment

ID: 38379049
OK, in D4: this should get you started... there may be a better option, but this should work.


Open in new window

Not an array formula... is best to avoid them if possible.

Author Comment

ID: 38379069
Hi Barman,

I applied your formula to the Sessions cells and it picked up the totals as they should have done. However, when I sort the three sheets (on Name field) the cell reference in the MATCH(Sessions! $A4... part of the formula doesn't change.

So, the problem still remains. The Sessions cell formulas are pointing at the cell references prior to the sort.

Any ideas ?
LVL 24

Expert Comment

ID: 38379188
1) Sort the whole range, including the formula.
2) Have the names in A4 etc referenced from another sheet... sort that sheet.
     e.g A4 = Sheet1!A4

Either of those will do the job.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 38379220
My process is as follows :

1. Sort the Name sheet range on column B.
2. Sort the Calendar sheet range on column B.
3. Sort the Sessions sheet range on column B.

I've attached the resulting file. You'll see the Sessions sheet formulas are pointing to the wrong references in the MATCH part of the formula eg. in cell D4....


$A21 should really read $A4
LVL 24

Expert Comment

ID: 38379300
The sort is not sorting past column C... so it is sorting A and keeping the ref.

If you highlight the whole rows 4 down... and sort on B it sorts OK.

You could make the whole formula volatile:

=COUNTIF(INDIRECT("Calendar!D"&MATCH(INDIRECT("Sessions!$A" & ROW()),Calendar!$A:$A,0)&":W"&MATCH(INDIRECT("Sessions!$A" & ROW()),Calendar!$A:$A,0)),Sessions!D$2)

Open in new window

LVL 50

Expert Comment

by:barry houdini
ID: 38379350
Best to avoid INDIRECT, not only because of it's volatility but because it requires you to "hardcode" the column letters into the formula (which won't change if you add or remove columns). Try this formula in D4 copied across and down


That should now sort OK whether you sort just columns A to C or the whole lot.....

regards, barry

Author Comment

ID: 38379379
Thanks Barry, that's done the trick. I knew I was sorting the whole range ok but I wasn't aware that INDIRECT won't update the cell references when the rows are sorted.

However, there's one outstanding problem. In some cells there are two codes (eg. SC) which, in fact are two separate codes, S and C, and should be treated as such. This is why I used FIND in the original formula. How can I incorporate FIND into your formula to deal with this combination of two codes in one cell.

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 38379525
OK, I missed that part - back to FIND within SUMPRODUCT, I'd suggest, to avoid array entry, i.e. this formula in D4 copied across and down


Note: wildcards within COUNTIF aren't an option - that would work for the letters but not numbers......

regards, barry

Author Closing Comment

ID: 38380732
Thanks to you both for your help and suggestions.
I'm going to have to go with Barry's solution as it works unconditionally.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question