Sorting an array

**lukela85** · 07-05-2010, 03:53 AM

Hi all,

I have a problem with an array. The array pulls all the data from one spreadsheet and displays it in a standings format in another. When I try to sort the standings I get the message 'You cannot change part of an array'. Is there a way round this?

Thanks in advance,

Luke

**Rahul Nagar** · 07-05-2010, 04:22 AM

Hi lukela85,

To cancel the error message, click OK, and then click the "X" button in the formula bar (or press ESC).

To determine which cells make up the array, use either of the following methods to highlight the range that makes up the array formula:

* Choose one of the cells that you know is in the array formula. On the Formula menu, click Select Special, and then click Current Array. In Microsoft Excel versions 5.0 and later, follow these steps:

1. On the Edit menu, click Go To. Or, press F5.

2. In the Go To dialog box, click Special.

3. In the Go To Special dialog box, click Current Array, and then click OK.

-or-
* Press CTRL+SHIFT and double-click a cell that you know makes up that array. After selecting the entire range that contains the array formula, edit the formula and press CTRL+SHIFT+ENTER if you are using Microsoft Excel for Windows, or COMMAND+RETURN if you are using Microsoft Excel for the Macintosh to reenter the array formula.

Regards
Rahul

**lukela85** · 07-05-2010, 03:02 PM

Thanks for your help. I think I'm right in saying though that by doing that I only identify the range of the array. Ideally what I'm looking to do is find a way to sort my standings, and because the data is held in an array I am unable to do that at present.

**shg** · 07-05-2010, 03:37 PM

Lukela, please don't quote whole posts -- it's just clutter.

Thanks.

**martindwilson** · 07-05-2010, 03:37 PM

youll probably have to unique rank the standings then use a vlookup or index/ match to sort

**lukela85** · 07-05-2010, 05:27 PM

Originally Posted by martindwilson

youll probably have to unique rank the standings then use a vlookup or index/ match to sort

How would I go about doing this?

**martindwilson** · 07-05-2010, 06:03 PM

show us your work book!

**lukela85** · 07-07-2010, 04:59 PM

I have tried uploading my worksheet, however the uploading tool keeps crashing on me. I see the limit is a 1000Kb file, and my file is 950Kb, so even though it is slightly smaller than the upper limit I am assuming this is probably why the uploader crashes, Can I reduce the file size to allow for upload?

**martindwilson** · 07-07-2010, 05:39 PM

zip it then you'll be ok
but
http://www.excelforum.com/excel-gene...amic-sort.html

**lukela85** · 07-09-2010, 05:35 PM

I have deleted some of the code so I was able to upload the worksheet, as I was having problems zipping it. It doesn't affect the standings worksheet though. Thanks for any further help