Sorting with Array formula

[mariannehislop]

I have the following array formula in my spreadsheet, however when I sort the data the reference 'Portable Register'!C7 acts like an "absolute" reference even though there is no $ $ in it, and ends upreferring to the wrong row.

{=MAX(IF('Completed Runs'!$D$4:$D$741='Portables Register'!C7,'Completed Runs'!$G$4:$G$740))}

Is there a trick to sorting array formulas?

[Jacc]

If you post a workbook with some sample data it will be much easier to help you.

[Speshul]

If you are sorting, you can use a LARGE/SMALL function instead of a MAX function

Example of it's use would be;

=LARGE(($A$1:$A$10=10)*ROW($1:$10),ROWS($A$1:A1))

Placed in cell B1 and dragged down, it will return which rows in A1:A10 have the value of 10. It will have the largest row on top, and descent as you drag it down.
The opposite can be achieved using SMALL instead of LARGE, you will have to eliminate the zeros if you go for SMALL
=SMALL(IFERROR((1/($A$1:$A$10=10)*ROW($1:$10)),""),ROWS($A$1:A1))

Not sure if this helps, but it's the best I can do without a sample file!

[mariannehislop]

The spreadsheet "Portables Register" is designed to record the service of portables. The formula looks at a street address in column B, finds that same address listed in the "completed runs" sheet and pulls the last day it was serviced.

Every time a portable is serviced it is added to the "completed runs" register, hence it is listed multiple times with difference service dates, so to pull the LAST, or MAX service date for a specific address I needed to use an array formula, using the little { } thingies.

When I sort the "Portables Register" the reference cell doesn't move with that row. Example the formula for row C27 will be looking at C347?

Are you confused yet?

[Jacc]

I am. Where are the spreadsheets?

[mariannehislop]

Sorry, new to this forum. How do I upload a workbook?

[mariannehislop]

Think I got it. Here it is

[FDibbins]

Your formula seems to be working fine. There are no matches for the C7:C27

[mariannehislop]

OK. The formula initiall was:

{=MAX(IF('Completed Runs'!$D$4:$D$741='Portables Register'!C7,'Completed Runs'!$G$4:$G$740))}

I changed the 'Portables Register'!C7 to and just =C7

{=MAX(IF('Completed Runs'!$D$4:$D$741=C7,'Completed Runs'!$G$4:$G$740))}

And it has no problem sorting.

Thanks for everyones help.

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.