Excel Match/Index not adjusted if database sorted?

[AndyKeen]

I have a database in Excel which consists of a couple of thousand rows or so which users need to sort, filter and manually amend regularly. I need to include a calculated value on each row based upon the results of a combined match,index,index formula as follows

=IFERROR(IF(ISBLANK(B2063),INDEX(SectorYield!$D$3:$N$5,MATCH(UniqueValues!F2063,SectorYield!$A$3:$A$5,0),MATCH(UniqueValues!D2063,SectorYield!$D$2:$M$2,1)),0),0)

The formula determines the value of the cell from a matrix held on another sheet. It all works perfectly until I use the Excel Filter/Sort function to re-order the database. The "UniqueValues!F2063" & "UniqueValues!D2063" (in this case) refer to the x and y values on the current row to be matched against the table elsewhere. BUT when the database is sorted the formulae are not modified to take account of the revised position of the row in the database and therefore the cell value reflects the x,y values for what is now in row 2063 instead of the x,y values of this row which has moved to say row 10 depending on the sort!

I hope this makes sense. I have obviously written the formula wrongly but I can't see how. Is there a way to use this kind of formula in a database or is it just a no no? If not possible, can anybody suggest a better way of handling it?

The problem is that the values in the table on the SectorYield sheet could change at any time as could the x,y values in the row on the UniqueValues database sheet but the cell in which the formula resides must always show the up to date and correct value automatically.

In short Help Please! I am stuck! Many thanks,

 

[SkipVought]

hi,

I am confused! Are you stating that when you sort the table, that each entire row is NOT included in the sort? That would be the only way the your data would loose the relationship between the MATCH row and the INDEX return value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AndyKeen]

No Skip. I am saying that If I have a database table spreading across 30 columns or so and that contains the row specific version of the above formula on each row it works perfectly. If I then click on the row ID for the headings and then data, filter it allows sort/filter on all columns of the table as you would expect. God help me if the user splits the table and sorts only one half!! Selecting a column and sorting highest to lowest causes the table to be re-ordered correctly BUT the formula references in the "index" parts of the formula each row (ie referring to other columns in the same row) are not updated in the process - so, it shows the wrong result on each row. Sorting back into the original sequence corrects most rows but the only way to solve the problem properly is to re-copy the formula back down the entire column from row 1 after every sort!! I am lost, but am now assuming it just can't be done? I am using Excel 2007 in case that makes a difference.

Thanks for responding so quickly.

 

[SkipVought]

lets get specific. what table is sorted, please? Sheet name and range. I assume its the SectorYield sheet, but the RANGE is critical!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I gonna work on the assumption that the range being sorted is on SectorYield in D3:N5 from your example code, the INDEX lookup range.

Column A was not sorted and it should as part of this lookup design!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AndyKeen]

No, that is not the case but I am going to put together a small test sheet to try to prove the problem one way or the other. I should have done so before wating your time - sorry. If I can reproduce the error in another smaller sheet I will repost here and (if I can) attach the sample workbook so that it is totally clear. Thanks for your patience - sometimes I just back myself into a corner!

 

[AndyKeen]

OK Found it - Although still not quite sure why it works as it does!

The original references in the matches were in the format "UniqueValues!F2063". "UniqueValues" is the current sheet that contains the formulae. Everything worked fine until the database is sorted.

I have modified the references to the format "F2063" and all now works perfectly!!!

Apologies for wasting your time. Should have worked it through easily enough on my own. Don't know why referring to the sheet as well as the cell stops the formula relationship working? I suppose it doesn't really matter though - just one of those things to remember I guess .

 

[SkipVought]

I have modified the references to the format "F2063" and all now works perfectly!!!

It all depends on what sheet the FORMULA resides.

BTW what you are referring to as a FORMAT is in reality a REFERENCE. A format is something entirely different in Excel lingo.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]